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

"Dave Peterson" wrote:

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?

HeatherO wrote:

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

--

Dave Peterson