Problems with variables and properties

[msfarrar]

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

[Aflatoon]

That error indicates that the value is not found, not an issue with the row variable.

[msfarrar]

After seeing your message, I checked to see what values it was using, and you are right the value was not in the area I was searching.

I have since updated it, so that it is searching for the correct value. I am still getting this error. Would I get that error if there are more than one of that value in my list? If so is there a way around it?

Thanks!

[Aflatoon]

No, if the value is there and it's a string (not stored as say a date or number) then your code should find it, although you really ought to specify all the options when doing a find (such as matchcase) since they persist from one find to another (including Finds done using the Ctrl+F dialog).