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

 

[Jon von der Heyden]

Does the message box not display a text description of the error?

 

[Dazzawm]

Does the message box not display a text description of the error?

No it doesnt

 

[Jon von der Heyden]

Well I'm stumped then. I can't recreate that behaviour.

 

[Dazzawm]

Well I'm stumped then. I can't recreate that behaviour.

I dont know if it makes any difference but there are 40000 rows of data it needs to look at.

 

[Jon von der Heyden]

How many NYA's?

 

[Dazzawm]

How many NYA's?

Probably about half.

 

[Jon von der Heyden]

I suspect this is the cause (although it wouldn't hurt if it actually gave us a meaningful error description! ).

I have something else up my sleeve. One mo....

 

[Jon von der Heyden]

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""),""""))")
        End With
    End With
End Sub

 

[Dazzawm]

That copied over as I wanted but.... It deleted everything in a cell in column AB that didnt have NYA in it

 

[Jon von der Heyden]

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