| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Vlookup with workbook defined in a variable
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
Heather
is the LookUp workbook open ? If not, I suspect that is your problem. Regards Trevor "HeatherO" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
Yes the LookUp workbook is open.
"Trevor Shuttleworth" wrote: Heather is the LookUp workbook open ? If not, I suspect that is your problem. Regards Trevor "HeatherO" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
Try this.
Workbooks("Auto Model Grid.xls").activate Worksheets("Sheet1").Activate -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
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 "Tom Ogilvy" wrote: Try this. Workbooks("Auto Model Grid.xls").activate Worksheets("Sheet1").Activate -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
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?). HeatherO wrote: 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 "Tom Ogilvy" wrote: Try this. Workbooks("Auto Model Grid.xls").activate Worksheets("Sheet1").Activate -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA/Vlookup with workbook defined in a variable
Hello guy's
there is free system called infolw .it's free and you can loaded all invenotry .It's good for somal buisenss even you can have some invoices in place if you wish . regards,Sadiq ------------------------------------------------------- Cheers, "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable sum from defined input | Excel Worksheet Functions | |||
| Vlookup using a variable for the workbook name | Excel Discussion (Misc queries) | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| [Q] msoShapeRectangle: variable not defined | Excel Programming | |||
| Problem with a defined variable | Excel Programming | |||
Hybrid Mode
