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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

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

How can't you control what's happening?
 

Forum statistics

Threads
1,170,942
Messages
5,872,857
Members
432,950
Latest member
ALeXceLBr

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