Code to check and match not working

**endoskeleton** · 05-25-2011, 12:05 PM

Hi

I have the following code that is supposed to check cell AC for "YES" , if that is correct then put data into AH:AT from the row immediately above it.

What am I doing wrong?

Thanks
Jon

Sub fillgap()
With ActiveSheet
LR = .Range("A" & .Rows.Count).End(xlUp).Row
If Range("AC" & Rw) = ("YES") Then
Range("AH:AT" & Rw) = Range("AH:AT" & Rw - 1)
End If
End With

End Sub

**davegugg** · 05-25-2011, 12:43 PM

What's the Rw variable? Shouldn't that be LR???

**endoskeleton** · 05-25-2011, 01:14 PM

Hi Dave

I'm very new to VBA, but on a steep learning curve.

I see what you mean but when I replace the Rw with LR, the macro runs but nothing happens.

It seems like it is finding no instances of "YES" within col AC.

The "YES" in Column AC is actually generated by a formula

 If(AB5=AB4,"YES,"")

and then copied down the column does that make a difference ?

Cheers

Jon

**davegugg** · 05-25-2011, 02:47 PM

Your code only looks at the last cell in Colum AC. If you want to look at every cell in the column, you have to loop through each of them:

Sub fillgap()

    Dim LR As Long

    With ActiveSheet
        For LR = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
            If Range("AC" & LR) = ("YES") Then
                Range("AH" & LR - 1 & ":AT" & LR - 1).Copy Destination:=Range("AH" & LR & ":AT" & LR)
            End If
        Next LR
    End With

End Sub

**endoskeleton** · 05-25-2011, 03:59 PM

Hi Dave,

Ahh I see,.

Didn't work the way at wanted at first until I realized that is was actually overwriting where I already had data. Once I changed the destination range it worked like a dream.

Thanks so much for your help.

Jon