Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
Can any one help whats wrong with it.
Code: Sub FindMin() 'variables to hold sheet name, starting and ending comparison rows and cols etc Dim sheet_name As String Dim starting_cell_row As Integer Dim starting_cell_col As Integer Dim ending_cell_row As Integer Dim starting_comparison_col As Integer Dim ending_comparison_col As Integer Dim resutling_column As Integer Dim smallest As Variant 'variables used in the loops Dim hold As Variant Dim hold_first As Variant Dim counter As Integer Dim col As Integer Dim temp As Variant 'initiating variable values sheet_name = "Results_Macro" starting_cell_row = 6 starting_cell_col = 7 ending_cell_row = 150 starting_comparison_col = 8 ending_comparions_col = 20 resutling_column = 21 smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value For counter = starting_cell_row To ending_cell_row 'starting and ending row for target col hold = ThisWorkbook.Sheets(sheet_name).Cells(counter,tart ing_cell_col).Value hold_first = Abs(hold - Cells(counter, starting_comparison_col).Value) smallest = Cells(starting_cell_row, starting_cell_col).Value For col = starting_comparison_col To ending_comparions_col temp = Abs(hold - Cells(counter, col).Value) If temp <= hold_first Then smallest = Cells(counter, col).Value hold_first = temp End If Next col ThisWorkbook.Sheets(sheet_name).Cells(counter, resutling_column).Value = smallest Next counter End Sub I am having this subscript out of range at the very first line of the code: smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value I have double checked the sheet name it is fine, it is getting the rows and col, but smallest remains empty. Someone plz help, Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
On Feb 19, 4:15 pm, Dave Peterson wrote:
Check the spelling on the sheet_name. Maybe there's an extra space in the name? Or maybe the underscore isn't really an underscore. wrote: Can any one help whats wrong with it. Code: Sub FindMin() 'variables to hold sheet name, starting and ending comparison rows and cols etc Dim sheet_name As String Dim starting_cell_row As Integer Dim starting_cell_col As Integer Dim ending_cell_row As Integer Dim starting_comparison_col As Integer Dim ending_comparison_col As Integer Dim resutling_column As Integer Dim smallest As Variant 'variables used in the loops Dim hold As Variant Dim hold_first As Variant Dim counter As Integer Dim col As Integer Dim temp As Variant 'initiating variable values sheet_name = "Results_Macro" starting_cell_row = 6 starting_cell_col = 7 ending_cell_row = 150 starting_comparison_col = 8 ending_comparions_col = 20 resutling_column = 21 smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value For counter = starting_cell_row To ending_cell_row 'starting and ending row for target col hold = ThisWorkbook.Sheets(sheet_name).Cells(counter,tart ing_cell_col).Value hold_first = Abs(hold - Cells(counter, starting_comparison_col).Value) smallest = Cells(starting_cell_row, starting_cell_col).Value For col = starting_comparison_col To ending_comparions_col temp = Abs(hold - Cells(counter, col).Value) If temp <= hold_first Then smallest = Cells(counter, col).Value hold_first = temp End If Next col ThisWorkbook.Sheets(sheet_name).Cells(counter, resutling_column).Value = smallest Next counter End Sub I am having this subscript out of range at the very first line of the code: smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value I have double checked the sheet name it is fine, it is getting the rows and col, but smallest remains empty. Someone plz help, Thanks -- Dave Peterson- Hide quoted text - - Show quoted text - Dave, thx for your help. I have checked that already. I am quite sure it is fine. I even copied and pasted in the code from the sheet. Nasir. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
That would usually mean that
sheet_name = "Results_Macro" is incorrect. You don't have a sheet named Results_Macro. It is possible that you have a space at the beginning or end of the name which would make it not match. Try renaming the sheet to make sure it is named Results_Macro. (upper and lower case are not important). -- Regards, Tom Ogilvy " wrote: Can any one help whats wrong with it. Code: Sub FindMin() 'variables to hold sheet name, starting and ending comparison rows and cols etc Dim sheet_name As String Dim starting_cell_row As Integer Dim starting_cell_col As Integer Dim ending_cell_row As Integer Dim starting_comparison_col As Integer Dim ending_comparison_col As Integer Dim resutling_column As Integer Dim smallest As Variant 'variables used in the loops Dim hold As Variant Dim hold_first As Variant Dim counter As Integer Dim col As Integer Dim temp As Variant 'initiating variable values sheet_name = "Results_Macro" starting_cell_row = 6 starting_cell_col = 7 ending_cell_row = 150 starting_comparison_col = 8 ending_comparions_col = 20 resutling_column = 21 smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value For counter = starting_cell_row To ending_cell_row 'starting and ending row for target col hold = ThisWorkbook.Sheets(sheet_name).Cells(counter,tart ing_cell_col).Value hold_first = Abs(hold - Cells(counter, starting_comparison_col).Value) smallest = Cells(starting_cell_row, starting_cell_col).Value For col = starting_comparison_col To ending_comparions_col temp = Abs(hold - Cells(counter, col).Value) If temp <= hold_first Then smallest = Cells(counter, col).Value hold_first = temp End If Next col ThisWorkbook.Sheets(sheet_name).Cells(counter, resutling_column).Value = smallest Next counter End Sub I am having this subscript out of range at the very first line of the code: smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value I have double checked the sheet name it is fine, it is getting the rows and col, but smallest remains empty. Someone plz help, Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
I would check the sheet name again. A space at the start or end of the name would be hard to see. Try reentering the sheet name on the sheet tab. Also, is the Results_Macro sheet in the same workbook as the code? You could try replacing "ThisWorkbook.Sheets... with ActiveWorkbook.Sheets... -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Can any one help whats wrong with it. Code: Sub FindMin() 'variables to hold sheet name, starting and ending comparison rows and cols etc Dim sheet_name As String Dim starting_cell_row As Integer Dim starting_cell_col As Integer Dim ending_cell_row As Integer Dim starting_comparison_col As Integer Dim ending_comparison_col As Integer Dim resutling_column As Integer Dim smallest As Variant 'variables used in the loops Dim hold As Variant Dim hold_first As Variant Dim counter As Integer Dim col As Integer Dim temp As Variant 'initiating variable values sheet_name = "Results_Macro" starting_cell_row = 6 starting_cell_col = 7 ending_cell_row = 150 starting_comparison_col = 8 ending_comparions_col = 20 resutling_column = 21 smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value For counter = starting_cell_row To ending_cell_row 'starting and ending row for target col hold = ThisWorkbook.Sheets(sheet_name).Cells(counter,tart ing_cell_col).Value hold_first = Abs(hold - Cells(counter, starting_comparison_col).Value) smallest = Cells(starting_cell_row, starting_cell_col).Value For col = starting_comparison_col To ending_comparions_col temp = Abs(hold - Cells(counter, col).Value) If temp <= hold_first Then smallest = Cells(counter, col).Value hold_first = temp End If Next col ThisWorkbook.Sheets(sheet_name).Cells(counter, resutling_column).Value = smallest Next counter End Sub I am having this subscript out of range at the very first line of the code: smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value I have double checked the sheet name it is fine, it is getting the rows and col, but smallest remains empty. Someone plz help, Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range
On Feb 19, 4:21 pm, Tom Ogilvy
wrote: That would usually mean that sheet_name = "Results_Macro" is incorrect. You don't have a sheet named Results_Macro. It is possible that you have a space at the beginning or end of the name which would make it not match. Try renaming the sheet to make sure it is named Results_Macro. (upper and lower case are not important). -- Regards, Tom Ogilvy " wrote: Can any one help whats wrong with it. Code: Sub FindMin() 'variables to hold sheet name, starting and ending comparison rows and cols etc Dim sheet_name As String Dim starting_cell_row As Integer Dim starting_cell_col As Integer Dim ending_cell_row As Integer Dim starting_comparison_col As Integer Dim ending_comparison_col As Integer Dim resutling_column As Integer Dim smallest As Variant 'variables used in the loops Dim hold As Variant Dim hold_first As Variant Dim counter As Integer Dim col As Integer Dim temp As Variant 'initiating variable values sheet_name = "Results_Macro" starting_cell_row = 6 starting_cell_col = 7 ending_cell_row = 150 starting_comparison_col = 8 ending_comparions_col = 20 resutling_column = 21 smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value For counter = starting_cell_row To ending_cell_row 'starting and ending row for target col hold = ThisWorkbook.Sheets(sheet_name).Cells(counter,tart ing_cell_col).Value hold_first = Abs(hold - Cells(counter, starting_comparison_col).Value) smallest = Cells(starting_cell_row, starting_cell_col).Value For col = starting_comparison_col To ending_comparions_col temp = Abs(hold - Cells(counter, col).Value) If temp <= hold_first Then smallest = Cells(counter, col).Value hold_first = temp End If Next col ThisWorkbook.Sheets(sheet_name).Cells(counter, resutling_column).Value = smallest Next counter End Sub I am having this subscript out of range at the very first line of the code: smallest = ThisWorkbook.Sheets(sheet_name).Cells(starting_cel l_row, starting_cell_col).Value I have double checked the sheet name it is fine, it is getting the rows and col, but smallest remains empty. Someone plz help, Thanks- Hide quoted text - - Show quoted text - Jim, Tom and Dave...........thanks a lot. I was using the right sheet name, however, as pointed out by one of you, i had saved the macro as personal macro and was trying to use it some workbook. When I changed to active workbook, it worked perfect. Once again, thank you all for pointing me in the right direction. Regards, Nasir. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
Subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming | |||
subscript out of range | Excel Programming | |||
SubScript Out Of Range. | Excel Programming |