VBA/Vlookup with workbook defined in a variable

[Guest]

I have this as my code:
Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.Worksheet.VLookup("CCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.WorksheetFunction.VLookup("DCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
Next counter
End With

For some reason when I do the vlookup I am getting an error message saying
the subscript is out of range. Is this because I am in another workbook that
it can't do the lookup on the other workbook or is it because I am trying to
string together a string plus a variable as the workbook's name? Any
thoughts would be welcome.
Thanks again in advance.
Heather O

 

[Trevor Shuttleworth]

Heather

is the LookUp workbook open ? If not, I suspect that is your problem.

Regards

Trevor

 

[Guest]

Yes the LookUp workbook is open.

Heather

is the LookUp workbook open ? If not, I suspect that is your problem.

Regards

Trevor

 

[Dave Peterson]

Maybe specifying the workbook that contains the Smart worksheet:

Sheets("SMART").Range("$A$2:$H$82")

becomes:

workbooks("myotherbook.xls").Sheets("SMART").Range("$A$2:$H$82")

in your vlookup() formula.

If that's not the problem, what line causes the error?

 

[Guest]

I did that and I still get the error:
run-time error 9: subscript out of range

Here is my code now as I have modified it but am still stumped on the
lookup. I have even hard coded the workbooks to make sure it looked at the
right ones, I don't know if I need to specify the path but I will try that
next.

Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
ActiveSheet.Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval,
Workbooks("CCS76463097.xls").Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
ActiveSheet.Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval,
Workbooks("DCS76463097.xls").Sheets("SMART").Range("$A$2:$H$82"), 7, 0)


End If
End If
Next counter
End With

 

[Tom Ogilvy]

Try this.

Workbooks("Auto Model Grid.xls").activate
Worksheets("Sheet1").Activate

 

[Guest]

Tried it and I am still getting the same error message. I've debuged it all
the way to the lookup function. It is returning all the correct values from
the active sheet but it just barfs when it gets to the code with the vlookup.
The workbook is open it just does not like it.
Thanks again for any help
Heather O

 

[Dave Peterson]

When I get that subscript out of range, it usually means that I've made a typo.

If you're code is breaking on that vlookup() line, I'd double/triple check the
spelling of the worksheet Smart (any extra spaces--leading or trailing?).

Tried it and I am still getting the same error message. I've debuged it all
the way to the lookup function. It is returning all the correct values from
the active sheet but it just barfs when it gets to the code with the vlookup.
The workbook is open it just does not like it.
Thanks again for any help
Heather O

 

[Guest]

Thanks Dave. I did check and I did have the sheet name incorrect. I guess
they changed the name of the sheet with my test workbook. However after I
made the changes I still got the errors. I've been looking it over and tried
changing the range and I am now at a complete loss. Funny because I have to
let them know how I am progressing with this project tommorrow. I'll keep
trying to plug away to see if I can find someway around this. Thanks though
it did help.
Heather

 

[Tom Ogilvy]

In the code you show, I believe only one thing can cause the error you cite:

run-time error 9: subscript out of range

This is if the name of one of the objects is incorrect. workbooks names
should always have .xls on the end (if you haven't maintained the correction
I suggested).

However, perhaps your error has changed. Post your latest code and indicate
which line is highlighted when you get the error and what the text and
number of the error is.

 

[Guest]

Hi Tom,
Here is my code as I run it now. I've highlighted the spot where it stops
With **** and I get the run-time error '9': Subscript is out of Range
message.
Any help is appreciated.
Heather
'get values from ccs and dcs and put in col N of Auto Grid
Workbooks("Auto Model Grid.xls").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
*****
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ccs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
****
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
End If
End If
Next counter
End With

 

[Tom Ogilvy]

when you use vlookup in VBA, you can't access a closed workbook.

There are clumsy workarounds, but is there some reason can't open the
workbook?

when opened, of course, it would be Workbooks("ccs76463097.xls").

--
Regards,
Tom Ogilvy

Hi Tom,
Here is my code as I run it now. I've highlighted the spot where it stops
With **** and I get the run-time error '9': Subscript is out of Range
message.
Any help is appreciated.
Heather
'get values from ccs and dcs and put in col N of Auto Grid
Workbooks("Auto Model Grid.xls").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
*****
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ccs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
****
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
End If
End If
Next counter
End With

 

[Guest]

The workbooks are open here is a copy of the full procedure so you can see
but I make sure the workbooks exist and are open before I do the lookup. I
just wrote out the full path name in case that was my problem in the vlookup.

Private Sub CommandButton1_Click()

Dim Dlrno As String
Dim Repno As String
Dim RSM As Integer
Dim Dlrshp As String
Dim PrepFor As String
Dim dlrrep As String
Dim ccswrkbk As Workbook
Dim dcswrkbk As Workbook
Dim wks As Worksheet
Dim lval As String
Dim rngscs As Range
Dim rngtouse As Range
Dim mycell As Range
Dim mainwks As Worksheet
Dim sPath As String
Dim sMyFile As String
Dim ccsfile As String
Dim dcsfile As String
Dim lokval As String
Dim debg As String


ccsfile = "no"
dcsfile = "no"


dlrrep = UserForm1.Dlrno + UserForm1.Repno


'Activate the correct worksheet based on English or French.
If EnglishButton1 Then

Workbooks("Pro Forma.xlt").Worksheets("English").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("English")

ElseIf FrenchButton1 Then


Workbooks("Pro Forma.xlt").Worksheets("French").Activate
With ActiveSheet
.Range("B1").Value = UserForm1.Dlrno
.Range("B2").Value = UserForm1.Repno
.Range("B1:B2").NumberFormat = "General"
.Range("B3").Value = UserForm1.PrepFor
.Range("B4").Value = UserForm1.Dlrshp
.Range("B5").Value = UserForm1.RSM
End With
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("French")

End If

'get values for both CCS and DCS and store in column "N" of Auto Model
Grid

Set wks = Workbooks("Auto Model Grid").Worksheets("sheet1")


sPath = "C:\Documents and Settings\My Documents\"
sMyFile = "CCS" & dlrrep & ".xls"
If Dir(sPath & sMyFile) = "" Then
ccsfile = "no"
Else: ccsfile = "yes"

End If

sMyFile = "DCS" & dlrrep & ".xls"
If Dir(sPath & sMyFile) = "" Then
dcsfile = "no"
Else: dcsfile = "yes"

End If


If ccsfile = "yes" Then
Set ccswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\CCS" & dlrrep & ".xls")
End If

If dcsfile = "yes" Then
Set dcswrkbk = Workbooks.Open("C:\Documents and Settings\My
Documents\DCS" & dlrrep & ".xls")
End If


'get values from dcs and ccs and put in col N of Auto Model Grid
Workbooks("Auto Model Grid.xls").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ccs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
lokval = Cells(counter, 2).Value
Cells(counter, 14).Value =
Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)
End If
End If
Next counter
End With



If EnglishButton1 Then
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("English")
ElseIf FrenchButton1 Then
Set mainwks = Workbooks("Pro Forma.xlt").Worksheets("French")
End If

UserForm1.Hide
Application.Visible = True

End

End Sub

 

[Dave Peterson]

If you're still getting that subscript error, you'll have to check your
typing--both the workbook name and worksheet name.

And this kind of format may be better (to stop the run time errors).

Cells(counter, 14).Value = _
Application.VLookup(lokval, _
Workbooks("ccs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"), _
7, False)

Dropping the .worksheetfunction. allows you to bring back the #n/a errors.

In this section, it looked like you had an extra leading space:

Application.WorksheetFunction.VLookup(lokval, Workbooks("C:\Documents and
Settings\My Documents\ dcs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"),
7, False)

(right before the dcs76... stuff.)

 

[Tom Ogilvy]

fine then use Workbooks("ccs76463097.xls").

 

[Guest]

Thank you so much that worked. Of course I do get the #N/A error if there is
no match found in the ccs76463097 workbook. Just off the top of your head is
there any way I can get around this or not really. I think there is
something though. I'll go search. I'm not too worried about it though.
Thanks so much.
Heather

 

[Dave Peterson]

One way:

dim Res as variant 'can contain an error

res = _
Application.VLookup(lokval, _
Workbooks("ccs76463097.xls").Sheets("SMART").Range("$A$2:$G$54"), _
7, False)

if iserror(res) then
Cells(counter, 14).Value = "missing"
else
cells(counter,14).value = res
end if

 

[Guest]

Thanks a million Dave. I really appreciate all your help with this. Wish me
luck with the rest of the project. This is just part A and then I have to
teach myself about using word with excel and mail merges and stuff. Nothing
like jumping in without checking how deep. Oh well, it's all learning. I
had no idea excel and vb could do all this stuff it's very exciting. I have
programmed in RPG and progress mostly so this is very new.
Thanks again.

 

[Dave Peterson]

Don't forget that there are MSWord newsgroups, too.

They're not as friendly <vbg>. But they're pretty darn smart!

Thanks a million Dave. I really appreciate all your help with this. Wish me
luck with the rest of the project. This is just part A and then I have to
teach myself about using word with excel and mail merges and stuff. Nothing
like jumping in without checking how deep. Oh well, it's all learning. I
had no idea excel and vb could do all this stuff it's very exciting. I have
programmed in RPG and progress mostly so this is very new.
Thanks again.

 

[Guest]

Thanks I won't forget. I think most people on these are pretty amazingly
smart and considerate to help out others who are stuck. This is definitely a
good resource.
Heather