VBA - Vlookup Macro with a glitch!

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Sprucy

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

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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

Board Regular
Joined
Oct 31, 2005
Messages
92
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Sprucy

How can't you control what's happening?
 

Forum statistics

Threads
1,136,504
Messages
5,676,254
Members
419,616
Latest member
quickflip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top