VBA - Vlookup Macro with a glitch!

[Sprucy]

Hi Everyone,

Sheet1, Row 1: Contains headers for Columns A to O.

1- I then copy / paste Raw Data into Rows 2 to 1000+?
2- Run Macro1 to tidy-up.
3- Run Macro2 (below) the finish the job...

This woks fine, but if I Run Macro2 before doing Step 1 & 2, my Header in L1 is replaced with the Vlookup Formula...
Why?

Part of Code from Macro2:
--------------------------------------------
With Worksheets("Sheet1")
lr = .Cells(65536, 1).End(xlUp).Row
.Range("L2").Formula = "=IF(ISNA(VLOOKUP(D2,Sheet3!$A$2:$K$10000,8,FALSE)),"""",(VLOOKUP(D2,Sheet3!$A$2:$K$10000,8,FALSE)))"
.Range("L2").Copy Destination:=.Range("L3:L" & lr)
End With
Application.ScreenUpdating = False
--------------------------------------------

Am I using the " lr (xlUp)" right?
Must be simple, just can't figure it out!
Any ideas would be great.

Cheers,
Sprucy

 

[Norie]

Sprucy

Wouldn't that be because there's no data so lr = 1, ie the header row.

 

[Sprucy]

Well, I tried changing this:

lr = .Cells(65536, 1).End(xlUp).Row
to:
lr = .Cells(65536, 2).End(xlUp).Row

thinking that it would help, obviously not.

Should I change the Destination Range?

Sprucy

 

[Norie]

Sprucy

Changing the 1 to the 2 just means you are looking in column B not C.

Why not just run the code in the proper order/at the proper time?

 

[HalfAce]

lr = .Cells(65536, 1).End(xlUp).Row
refers to the row of the last used cell in column A.

lr = .Cells(65536, 2).End(xlUp).Row
refers to the row of the last used cell in column B.

The statement:
.Range("L2").Copy Destination:=.Range("L3:L" & lr)
will copy L2 and paste to the range L1:L3 if there is no data in the column used.

Does that help clear things up a bit?

(The only way I can reproduce the results you're getting is if like Norie says, there is
no data (except a header) in the column used to define your variable lr.)

 

[Sprucy]

Hi Norie,

Most of the Macros run with the "Call" statement, but I really need a break-in-code at this point.

End Users will be using a Userform complete the many steps involved, which I cannot control.

I added a If A2 ="" then End, seems to work for now. A:A should never be "".
But with my luck, someone will delete A2 and run Macro2...
1% chance, I was just trying to cover all angles.

Thanks anyway.

 

[Norie]

Sprucy

How can't you control what's happening?