VBA Insert Name and Vlookup

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Hello Guys,

I would like to know what is the problem with the below code. Basically, I am doing a vlookup on V2 by looking up the value of D4 from inserted name("salesManagers"). I hope to do Autofilldown for V column and copy/paste special value.




Sub Test()

Dim iiii as Long

'Insert Range Names
Sheets("Vendorlist").Range("A:B").CurrentRegion.Name = "SalesManagers"

'vlookup

For iiii = 2 To Range("A65536").End(xlUp).Row

Range("V2") = Application.VLookup(Cell(iiii, 4), Range("SalesManagers"), 2, 0)

Next iiii

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Range("V2") = Application.VLookup(Cell(iiii, 4), Range("SalesManagers"), 2, 0)

I am not the best at VBA by any stretch of the imagination but try:

Range("V2") = WorksheetFunction.VLookup(Cell(iiii, 4), Range("SalesManagers"), 2, 0)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
There is no such thing as Cell.

Try Cells.
Code:
 Range("V2") = Application.VLookup(Cells(iiii, 4), Range("SalesManagers"), 2, 0)
By the way why not just put the VLOOKUP formula in the cells?
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
See Norie is Awesome!
I am just an amateur in this realm of pros!!!!!!

Michael
 

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Thank you Norie and Michael!
I got it right from cells.

Norie, I will be generating this report on a daily basis, and I thought it will be easier for me if I have the vlookup already included in the part of the "formatting procedures" and also I am trying to learn the basics of VBA. I am learning great from studying your answers on this forum!!!
 

Forum statistics

Threads
1,144,162
Messages
5,722,845
Members
422,460
Latest member
VBA_Noob01

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