Error in code when I run as usual but works correctly when I step through it ?

[Pauljj]

I use some code to update data on a daily basis. It copies data from one tab and pastes to another. To determine what row I want to paste to I use this code

this by the way is a snippet

Worksheets("Booking Data").Activate
    
    wkno = Format(Sheets("Input").Range("O6"), "DD/MM/YYYY")
    rno = Range(Cells(2, 2), Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1
    Application.Goto Reference:="BOOKTRANS"
    Selection.Copy
    Sheets("Booking Data").Activate
    Range("b1").Select
    ActiveCell.Offset(rno, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Transpose:=True

If I run the code as usual 'rno' = 0

If I step through the code 'rno' = 631 which is what it should

I am confused and slowly getting frustrated. Can anyone advise a possible reason please ?

Thanks
Paul

 

[mole999]

maybe qualify the range
rno = Range(Cells(2, 2), Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1
to
rno = Worksheets("Booking Data").Range(Cells(2, 2), Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1

 

[Pauljj]

Sadly it didn't make any difference

 

[Pauljj]

I've worked out the cause just unsure why it doesn't like it

The problem was with the 'cells(2,2)

I changed this code

rno = Range(Cells(2, 2), Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1

to this

rno = Worksheets("Booking Data").Range("B2:B5000").Find(wkno, LookIn:=xlValues).Row - 1

And it now works. Very confused

 

[Fluff]

You need to qualify the cells as well as the range

    rno = Worksheets("Booking Data").Range(Worksheets("Booking Data").Cells(2, 2), Worksheets("Booking Data").Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1

 

[Pauljj]

Actually no my efforts and others still don't work

This is the code I am using

    wkno = Format(Sheets("Input").Range("O6"), "DD/MM/YYYY")
    rnob = Worksheets("Booking Data").Range(Worksheets("Booking Data").Cells(2, 2), Worksheets("Booking Data").Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1
    Application.Goto Reference:="BOOKTRANS"
    Selection.Copy
    Sheets("Booking Data").Activate
    Range("b1").Select
    ActiveCell.Offset(rnob, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Transpose:=True

It still returns zero as the row number which is incorrect.....the odd thing is this code does work

    wkno = Format(Sheets("Input").Range("O6"), "DD/MM/YYYY")
    rno = Range(Cells(2, 2), Cells(5000, 2)).Find(wkno, LookIn:=xlValues).Row - 1
    Application.Goto Reference:="STATTRANS"
    Selection.Copy
    Sheets("Daily Data").Activate
    Range("b1").Select
    ActiveCell.Offset(rno, 1).Select
    Selection.PasteSpecial Paste:=xlValues, Transpose:=True

Mind boggling !?!?!?

Also why would the code at the top work when I step through it but not if I let it run....makes it very hard to see the issue

 

[Fluff]

It may depend on what the rest of your macro is doing. As well as if you have lots of formulae.
When stepping through the code, xl has a chance to update formulae, pivots etc, but when running the code normally that doesn't always happen.

 

[mole999]

does Daily Data have lots of formulas ? pop a DoEvents in the code, let it get the time it needs