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

subscript out of range
 

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

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.

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

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

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.