Help with a conditional copy paste code

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
THanks for LOOKING!!!!!!!!!!
Here is my situation....

Column AS is 6 to 83 rows
Column AQ is 6 to 83 rows

AS = Yes or No in any cell 6 through 83
AQ = 1-8 in any cell 6 through 83

If AS6 = yes and AQ6 =1 Then nothing
If AS6 = yes and AQ6 =2 Then Copy (E6:F6) then paste to (AU6:AV6)
If AS6 = yes and AQ6 =3 Then Copy (E6:F6) then paste to (AU6:AV6) & (Aw6:AX6)
If AS6 = yes and AQ6 =4 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6
If AS6 = yes and AQ6 =5 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6
If AS6 = yes and AQ6 =6 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6
If AS6 = yes and AQ6 =7 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6 & BE6:BF6

I have had a tough time coming up with code to perform this. Also,I would need code like this for each cell 6 through 83 is there any other way (Shorter)?

THANKS AGAIN!!!!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
On 2002-10-15 16:57, Lowell In the south wrote:
THanks for LOOKING!!!!!!!!!!
Here is my situation....

Column AS is 6 to 83 rows
Column AQ is 6 to 83 rows

AS = Yes or No in any cell 6 through 83
AQ = 1-8 in any cell 6 through 83

If AS6 = yes and AQ6 =1 Then nothing
If AS6 = yes and AQ6 =2 Then Copy (E6:F6) then paste to (AU6:AV6)
If AS6 = yes and AQ6 =3 Then Copy (E6:F6) then paste to (AU6:AV6) & (Aw6:AX6)
If AS6 = yes and AQ6 =4 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6
If AS6 = yes and AQ6 =5 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6
If AS6 = yes and AQ6 =6 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6
If AS6 = yes and AQ6 =7 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6 & BE6:BF6

I have had a tough time coming up with code to perform this. Also,I would need code like this for each cell 6 through 83 is there any other way (Shorter)?

THANKS AGAIN!!!!

I think this code will work for you (test to make sure):

<PRE>
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).FormulaR1C1 = _
"=INDIRECT(""R""&ROW()&""C""&6-(EVEN(COLUMN())-COLUMN()),0)" & _
"*(RC43>TRUNC((COLUMN()-COLUMN(RC47))/2)+1)"
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).Copy
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).Replace _
What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

</PRE>

Regards,
 

Forum statistics

Threads
1,144,281
Messages
5,723,471
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top