Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable causing subscript out of range error
I am trying to saving a file based on the text name in a designated cell in
the workbook. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 A similar macro works fine: Sub update4() 'defines user file in fxRM_Update.xls Need to insert this everytime you reactivate fxRM_Update and want to refer back to user file. Dim bk As Workbook, bk1 As Workbook Dim sstr As String Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) bk1.Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable causing subscript out of range error
Your issue is gong to be that there is no workbook open that matches the
value sstr. Add a message box just before the line that sets the object reference to confirm the value of sstr. You will need it to match exactly to the name of the open workbook. Check for blank spaces and such... sstr = bk.Worksheets("lookup").Range("d39").Value msgbox sstr 'Check the value of the variable Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS -- HTH... Jim Thomlinson "Andyjim" wrote: I am trying to saving a file based on the text name in a designated cell in the workbook. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 A similar macro works fine: Sub update4() 'defines user file in fxRM_Update.xls Need to insert this everytime you reactivate fxRM_Update and want to refer back to user file. Dim bk As Workbook, bk1 As Workbook Dim sstr As String Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) bk1.Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming | |||
Variable-Length String causing error | Excel Programming |