|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
VBA/Vlookup with workbook defined in a variable
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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
VBA/Vlookup with workbook defined in a variable
fine then use Workbooks("ccs76463097.xls").
-- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 |
VBA/Vlookup with workbook defined in a variable
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" wrote: 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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
VBA/Vlookup with workbook defined in a variable
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 HeatherO wrote: 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" wrote: 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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
VBA/Vlookup with workbook defined in a variable
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" wrote: 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 HeatherO wrote: 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" wrote: 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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
VBA/Vlookup with workbook defined in a variable
Don't forget that there are MSWord newsgroups, too.
They're not as friendly <vbg. But they're pretty darn smart! HeatherO wrote: 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" wrote: 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 HeatherO wrote: 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" wrote: 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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
VBA/Vlookup with workbook defined in a variable
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 "Dave Peterson" wrote: Don't forget that there are MSWord newsgroups, too. They're not as friendly <vbg. But they're pretty darn smart! HeatherO wrote: 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" wrote: 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 HeatherO wrote: 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" wrote: 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$5 4"), 7, False) (right before the dcs76... stuff.) HeatherO wrote: 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$5 4"), 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 "Tom Ogilvy" wrote: 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 "HeatherO" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "HeatherO" wrote in message ... 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 |
| All times are GMT +1. The time now is 05:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com