Finding row number with .find()

[treasuresflemar]

CP will always be found in column "A"
There will always be only one occurrence of CP in the range.
It will always be found.
Need to see if the sum of columns "B" through "L" in the row CP is
found in = 0 if so then checkpost = True
All columns B:L are formated as currency

What I need x to do is return the row number

Set x = Range("sales" & CD).Find(CP)

Thanks Carl


Function Checkpost(CP As Date, CD As String)
'CP is the date that is being posted
'CD is the Year being posted which is part
'of the named range
'Checkpost returns True if no posting has been made
'to the posting date

Dim x as Integer
dim y

With Worksheets("sales " & CD)
'sets the sheet the data is on for the year invloved

Set x = Range("sales" & CD).Find(CP)
'selects the correct range Need to get row number

y = Range("b" & x, "l" & x).Subtotal

End With

If y = 0 Then
Checkpost = True
'Nothing posted to that day

Else
Checkpost = False
'A posting to that date has been done
End If
End Function

 

[JLGWhiz]

I would think that x.Row would work:

Set x = Range("sales" & CD).Find(CP)
'selects the correct range Need to get row number
MsgBox = x.Row

 

[Jim Thomlinson]

When you do a find you need to specify most of the optional arguments. If you
don't specify then they system will use whatever the last setting set by the
user. Additionally while you have said that the value will always be found,
you are still better off to check to see if it was actually found (as opposed
to generating an error) so something like this...

Dim rngFound As Range
Dim lng As Long

Set rngFound = Columns("A").Find(What:=cp, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox cp & " not found"
Else
lng = rngFound.Row
'Carry On
End If