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.

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

Thanks actually you helped alot. I just have one other question if I am
opening those files the ABC123999 or the DEF123999 I was just wondering if
there is anyway I can check before I open them to make sure they are there so
that I don't get an error when I try to open a workbook that is not there? I
am supposed to go under the assumption that they exist but I am trying to
capture the human error which is bound to be there.
Thanks in advance for all your help.


"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.

Sub DoesFileExist()
Dim sPath As String
Dim sMyFile As String

sPath = "C:\Documents and Settings\My Docs\"
sMyFile = "ABC123999.xls"

If Dir(sPath & MyFile) = "" Then
MsgBox "File NOT FOUND"
Else
MsgBox "File EXISTS"
End If

End Sub


"HeatherO" wrote:

Thanks actually you helped alot. I just have one other question if I am
opening those files the ABC123999 or the DEF123999 I was just wondering if
there is anyway I can check before I open them to make sure they are there so
that I don't get an error when I try to open a workbook that is not there? I
am supposed to go under the assumption that they exist but I am trying to
capture the human error which is bound to be there.
Thanks in advance for all your help.


"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.

gocush gave you the dir() solution for checking for existence.

But you may want to consider using
application.getopenfilename

to prompt the user to point at the workbook that should be opened. I find
pointing and clicking a little safer way to get input from users (like me!).



HeatherO wrote:

Thanks actually you helped alot. I just have one other question if I am
opening those files the ABC123999 or the DEF123999 I was just wondering if
there is anyway I can check before I open them to make sure they are there so
that I don't get an error when I try to open a workbook that is not there? I
am supposed to go under the assumption that they exist but I am trying to
capture the human error which is bound to be there.
Thanks in advance for all your help.

"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