Please help.. VLookup Macro
 

Rich,

First a mild caution - Please don't attach anything to your message. People
won't touch it and you won't get many replies - if any...

Now - the code I might use might look like this:

Dim cel as Range, rng1 as Range, rng2 as Range, rng3 as range

Set rng1 = Workbooks("MyBook").Worksheets("VENTURE").Range("A 10:50")
Set rng2 = Workbooks("C:\VRMList.xls").Worksheets("VRM
BYNUMBER").Range("B3:C140")
Set rng3 = Workbooks("C:\VRMList.xls").Worksheets("VRM
BYNUMBER").Range("B:B")

For each cel in rng1
If worksheetfunction.Countif(rng3,cel) 0 then
cel = WorksheetFunction.Vlookup(cel,rng2,2,False)
else
msgbox "Product Code not found"
End If
Next

--
steveB

Remove "AYN" from email to respond
"Rich Foreman" wrote in message
...
Hi,

I have two workbooks. One is the one the macro needs to be run on, but
it needs to get data from another workbook.

One worksheet 1, We have a list of our forumulas. To protect ourselves,
these only use the product code in column A, and not the product name.

On workbook 2, we have a list of product codes in column B and column C
is the products name.

Out office manager wants to print out workbook 1, but first he wants the
product code replaced with the product name.

Here is what I tried so far, but I get an error in this code:

Sub ReplaceVRMWithName()

Dim r1 As Range
Dim x As Long

Set r1 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B3:C140")

(In that, I am trying to open up the second workbook and get the list of
product codes and names into a range. this is where my error is
currently)

For x = 10 To 50
With Worksheets("VENTURE")
.Range("A" & x).Value = Application.VLookup( _
.Range("A" & x), r1, 2, False)
End With
Next

(here, I am trying to replace the product codes in workbook 1 with the
product name. THe codes are in cells A10 - A50. Is this how I am
supposed to do this?)

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

Please help.. VLookup Macro
 

Thanks Steve,

I tried this code, and replaced 'MyBook' with the correct path to the
workbook I am working on.

I still get the same error as before, which is 'subscript out of range'.

I am getting it on these three lines:

Set rng1 =
Workbooks("C:\InvControl.xls").Worksheets("Venture ").Range("A10:A60")
Set rng2 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B3:C140")
Set rng3 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B:B")

Thanks,

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Please help.. VLookup Macro
 

Rich,

I can't be sure - but make sure each Set rng is on a single line
Set rng1
=Workbooks("C:\InvControl.xls").Worksheets("Ventur e").Range("A10:A60")

If you must wrap - use the line continuation "_"
Set rng1 = _
Workbooks("C:\InvControl.xls").Worksheets("Venture ").Range("A10:A60")

I check this code to see if it worked correctly (notice line continuation)
Dim rng1 As Range
Set rng1 = Workbooks("book4").Sheets("sheet1").Range("A:B")
'
MsgBox
WorksheetFunction.VLookup(Workbooks("book2").Sheet s("Sheet1").Range("A1"), _
rng1, 2, False)


So instead of Workbooks("C:\InvControl.xls").
use Workbooks("InvControl").
--
steveB

Remove "AYN" from email to respond
"Rich Foreman" wrote in message
...


Thanks Steve,

I tried this code, and replaced 'MyBook' with the correct path to the
workbook I am working on.

I still get the same error as before, which is 'subscript out of range'.

I am getting it on these three lines:

Set rng1 =
Workbooks("C:\InvControl.xls").Worksheets("Venture ").Range("A10:A60")
Set rng2 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B3:C140")
Set rng3 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B:B")

Thanks,

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Please help.. VLookup Macro
 

the most obvious error to me is that

Workbooks("C:\InvControl.xls").

is illegal. the workbooks collection only contains open workbooks and you
index them with the name

Workbooks("InvControl.xls")

and InvControl.xls must be open.

--
Regards,
Tom Ogilvy

"STEVE BELL" wrote in message
news:F26Ie.12604$Bx5.1344@trnddc09...
Rich,

I can't be sure - but make sure each Set rng is on a single line
Set rng1
=Workbooks("C:\InvControl.xls").Worksheets("Ventur e").Range("A10:A60")

If you must wrap - use the line continuation "_"
Set rng1 = _

Workbooks("C:\InvControl.xls").Worksheets("Venture ").Range("A10:A60")

I check this code to see if it worked correctly (notice line continuation)
Dim rng1 As Range
Set rng1 = Workbooks("book4").Sheets("sheet1").Range("A:B")
'
MsgBox
WorksheetFunction.VLookup(Workbooks("book2").Sheet s("Sheet1").Range("A1"),
_
rng1, 2, False)


So instead of Workbooks("C:\InvControl.xls").
use Workbooks("InvControl").
--
steveB

Remove "AYN" from email to respond
"Rich Foreman" wrote in message
...


Thanks Steve,

I tried this code, and replaced 'MyBook' with the correct path to the
workbook I am working on.

I still get the same error as before, which is 'subscript out of range'.

I am getting it on these three lines:

Set rng1 =
Workbooks("C:\InvControl.xls").Worksheets("Venture ").Range("A10:A60")
Set rng2 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B3:C140")
Set rng3 = Workbooks("C:\VRMList.xls").Worksheets("VRM BY
NUMBER").Range("B:B")

Thanks,

Rich

*** Sent via Developersdex http://www.developersdex.com ***