msfarrar
09-10-2012, 05:46 AM
I have two separate functions I created in my excel spread sheet. Both of these functions take a string from one part of the sheet and then determine which row that value is in another spreadsheet. The working function returns the row number, and the other function returns the text of a specific cell.
In the second function I accidentaly dimensionalized row as an integer, then realized that I didnt want to do that. Since I have done that, my new function does not run and gives me a runtime error 91. The other function works just fine.
All help is greatly appreciated!!!
Mattie
Working Function:
Public Function matchAOS(AOS As String) As Integer
'Enter the AOS to determine which column that effectivity is in the buyoff spreadsheet
Dim searchRange As Range
Dim findRow As Range
'Determines which row that AOS is in the buyoff hours pivot
Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Pivot").Activate
Set searchRange = Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Pivot").Range("A4:A1000")
Set findRow = searchRange.Find(AOS, LookIn:=xlValues, Lookat:=xlWhole)
matchAOS = findRow.row
End Function
The Function that doesn't work. It fails at counter = findRow.row, which happens successfully with the working function above.
Public Function highDriver(AOS As String) As String
'Enter effectivity to determine which column that effectivity is in the
buyoff spreadsheet
Dim searchRange As Range
Dim findRow As Range
Dim counter As Integer
'Determines which row that AOS is in the buyoff hours pivot
With Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Buyoff")
Set searchRange = .Range("D2:D1000")
Set findRow = searchRange.Find(AOS, LookIn:=xlValues, Lookat:=xlWhole)
counter = findRow.row
highDriver = .Cells(counter, 3).Text
End With
End Function
In the second function I accidentaly dimensionalized row as an integer, then realized that I didnt want to do that. Since I have done that, my new function does not run and gives me a runtime error 91. The other function works just fine.
All help is greatly appreciated!!!
Mattie
Working Function:
Public Function matchAOS(AOS As String) As Integer
'Enter the AOS to determine which column that effectivity is in the buyoff spreadsheet
Dim searchRange As Range
Dim findRow As Range
'Determines which row that AOS is in the buyoff hours pivot
Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Pivot").Activate
Set searchRange = Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Pivot").Range("A4:A1000")
Set findRow = searchRange.Find(AOS, LookIn:=xlValues, Lookat:=xlWhole)
matchAOS = findRow.row
End Function
The Function that doesn't work. It fails at counter = findRow.row, which happens successfully with the working function above.
Public Function highDriver(AOS As String) As String
'Enter effectivity to determine which column that effectivity is in the
buyoff spreadsheet
Dim searchRange As Range
Dim findRow As Range
Dim counter As Integer
'Determines which row that AOS is in the buyoff hours pivot
With Workbooks("HH-60M Hours.xlsx").Worksheets("HH-60M Buyoff")
Set searchRange = .Range("D2:D1000")
Set findRow = searchRange.Find(AOS, LookIn:=xlValues, Lookat:=xlWhole)
counter = findRow.row
highDriver = .Cells(counter, 3).Text
End With
End Function