I'm kind of confused, but maybe this will help (or hurt!).

Option Explicit
Sub testme()

Dim res As Variant
Dim wkbkABC As Workbook
Dim wkbkDEF As Workbook
Dim wks As Worksheet
Dim rngABC As Range
Dim rngDEF As Range
Dim rngToUse As Range
Dim myCell As Range
Dim myNumber As String

myNumber = "1234123"

Set wks = Workbooks("test.xls").Worksheets("sheet1")

'some way to get the name and open the workbooks...
Set wkbkABC = Workbooks.Open(Filename:="c:\my docs\ABC" & myNumber & ".xls")
Set wkbkDEF = Workbooks.Open(Filename:="c:\my docs\DEF" & myNumber & ".xls")

With wkbkABC.Worksheets("Smart")
Set rngABC = .Range("A:H")
End With

With wkbkDEF.Worksheets("smart")
Set rngDEF = .Range("a:h")
End With

For Each myCell In wks.Range("B2:B55").Cells
'check 10 columns over (column L)
If UCase(myCell.Offset(0, 10).Value) = "ABC" Then
Set rngToUse = rngABC
Else
Set rngToUse = rngDEF
End If
res = Application.VLookup(myCell.Value, rngToUse, 7, False)
If IsError(res) Then
'not found, put error msg in column C (.offset(0,1)
myCell.Offset(0, 1).Value = "not found"
Else
myCell.Offset(0, 1).Value = res
End If
Next myCell

End Sub



HeatherO wrote:

I am currently trying to create a macro that has a prompt for people to enter
values. Based on these values the macro will then open 2 workbooks with
those values plus 2 different prefixes (ie. ABC123999 and DEF123999). In
another workbook I am looking up based on the one column in this workbook if
it has ABC or DEF I am trying to do a vlookup to the appropriate workbook
and then return the value of the 7th column in either the ABC or DEF workbook
to the N column of the original workbook.
I know I can do this in Excel but when I try to create the code in VBA I
am just not getting it. This is what I have so far:
Workbooks("Test").Worksheets("Sheet1").Activate
With ActiveSheet
.Range("N2:N55").Value =

If .Range("L2:L55") = "ABC" Then
VLOOKUP(B2,workbooks("ABC123999").worksheets("SMAR T")
$A$2:$H$H82,7,FALSE),0)
Any help would be gratefully appreciated.

--

Dave Peterson