subscript out of range

[Nasir.Munir]

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_cell_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,tarting_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_cell_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]

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.

 

[Nasir.Munir]

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.




















--

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.

 

[Guest]

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).

 

[Jim Cone]

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


<[email protected]>
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_cell_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,tarting_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_cell_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

 

[Nasir.Munir]

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


















- 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.