CountIf function

[Lolly]

When I am writing this code.
Error is subscript out of Range
Code is as Follows

Sub FindBillyBrown35()
Dim vOurResult
Dim rFoundIt As Range
Dim iLoop As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
With Sheet1.Range("Data")
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 1 To WorksheetFunction.CountIf _
(Sheet1.Range("Data"), "Billy Brown")

'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:="Billy Brown", After:=rFoundIt, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'Check if it's the right one.
If rFoundIt.Offset(0, -1).Value = 35 Then
vOurResult = rFoundIt.Offset(0, 3)
Exit For 'Leave loop
End If

Next iLoop
End With 'Sheet1.Range("Data")

If Not IsEmpty(vOurResult) Then 'Variable holds a value
MsgBox vOurResult
End If
End Sub
Any help would be appreciated

 

[Chris Lavender]

Hi Kittie (Lolly)
This runs fine on my PC, both for XL97 and XL2000. I can generate other
errors by changing the Data range and changing the value types, but not
'subscript out of Range'...
What happens when you step through the code? What line does it stall on?
Best rgds
Chris

 

[Lolly]

Hi
Chris
The problem is it's not giving me any output result .

If you help me out with that

thanx

 

[Tom Ogilvy]

if
rFoundIt.Offset(0, 3)

is empty for Offset(0,-1) = 35 , then your code will show nothing. If not,
it appears to work.

 

[Lolly]

Hi
Tom

Thanx a lot
I checked it that cell value is not empty

but still no output

 

[Tom Ogilvy]

Best I can do is test it and it worked for me. Maybe change xlWhole to
xlPart - you might have added spaces.