Code Please That Could Save Me Hours

[Dazzawm]

I need a code that will look at any data that is in column AG and copy it 1 row up and 5 columns to the left (AB) but only if the destination cell has NYA in it. Is this possible please? Thanks

 

[Dazzawm]

whooooops

Public Sub OogaBoogaSnort()
    With ActiveSheet
        With Intersect(.Columns("AB"), .UsedRange)
            .Value = Evaluate("if(row(),if(" & .Address & "=""NYA"",if(len(" & .Offset(1, 5).Address & ")," & .Offset(1, 5).Address & ",""NYA"")," & .Address & "))")
        End With
    End With
End Sub

Thanks I think you cracked it!

 

[Dazzawm]

Hi Jon. I need this code now to copy across 5 columns as it is now BUT down one row instead of up 1. I thought i could change the offsets to -1 instead of 1 but it comes up with the 400 error again. Thanks.

 

[Jon von der Heyden]

Do as you were but change the Intersect statement to:

Intersect(.Columns("AB"), .UsedRange[B][COLOR="Red"].Offset(1)[/COLOR][/B])

The error was because previously the range this referred to began in row #1, and you can't have -1 row from that row position.

 

[Dazzawm]

Do as you were but change the Intersect statement to:

Intersect(.Columns("AB"), .UsedRange[B][COLOR=red].Offset(1)[/COLOR][/B])

The error was because previously the range this referred to began in row #1, and you can't have -1 row from that row position.

Thanks I will try tomorrow when back at work.

 

[Dazzawm]

Works great, thanks.