Help with a conditional copy paste code

[Lowell In the south]

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!!!!

 

[Barrie Davidson]

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,