cell.find command in excel (VBA)

[ckaren000]

hi people,
i am writing a macro in which i want to search for particular number starting with '3' in a column (say for instance C). I know, using 'cell.find' we can search it. but i don't know how to use it and more over i want to stop searching once it reaches a blank cell.
thanks in advance
karen

 

[Turpis]

Hi Karen,
I know that this does not use the 'cell.find' but here is what I would do, based on your description of the problem.

Public Sub TestFind()
Sheet1.Range("C1&quot.Select
Do Until Left(ActiveCell.Value, 1) = 3
If ActiveCell.Value = "" Then
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "Found " & ActiveCell.Value & vbCrLf & _
"at " & ActiveCell.Address
End Sub

Charles
Walden's Machine, Inc.
Quality Assurance/Office Developer

 

[SkipVought]

Karen,

I try to NOT select or activate objects in Excel unless I need to (faq707-4105 How Can I Make My Code Run Faster?)

Here's another approch...

Function MySearch(rng As Range) As Long
    With rng
      r = .Row
      c = .Column
    End With
   Do
     If Left(Cells(r, c).Value, 1) = "3" Then Exit Do
     r = r + 1
   Loop
   MySearch = Cells(r, c).End(xlDown).Row + 1
End Function

returns the row number that you are looking for.

hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ckaren000]

thanks guys