Finding row number with .find()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

treasuresflemar - 10 Dec 2007 22:25 GMT

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

Reply to this Message

JLGWhiz - 10 Dec 2007 22:55 GMT

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

> CP will always be found in column "A"
> There will always be only one occurrence of CP in the range.
[quoted text clipped - 38 lines]
> End If
> End Function

Reply to this Message

Jim Thomlinson - 10 Dec 2007 23:37 GMT

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

Signature

HTH...

Jim Thomlinson

> I would think that x.Row would work:
>
[quoted text clipped - 44 lines]
> > End If
> > End Function

Reply to this Message