.find

Please help me,
how to find exactly only one string.
For example I would like to find only these ranges which have only "A" not "A...".
here is part of VBA code :
Temp = "A"
Set d(j) = .Find(Temp, LookIn:=xlValues)
If Not d(j) Is Nothing Then
firstAddress = d(j).Address
Do
Total1(j) = Total(j) + 1
Set d(j) = .FindNext(d(j))
d(j).Select

Loop While Not d(j) Is Nothing And d(j).Address <> firstAddress

End If
How to reach it ?
Thank You.

The HelpHeap seems to be fouled up for the '.Find' function:

Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn’t affect the selection or the active cell.

Well, Megashaft notwithstanding, I couldn't get '.Find' to return a Range Obect.

Does anyone know how to actually get the Range object from .Find???

Cells.Find seems to return the text from the cell that was fouind, but not a range object. Debugging indicates that VBA wants to put a Variant/String here.

Cells.Find.Address does return a string with the address of the found cell, but I couldn't figure out how to use the "Is Nothing" test on it.

Here is the code I was playing with, but it doesn't quite work. I commented out 'Option Explicit' and the Dim for aFound so that VBA could define the data type returned by .Find. Insert a breakpoint and look in the 'Locals' window to see what data type was assigned by VBA:

Code:

---

'Option Explicit
Sub Macro1()
'Dim aFound As String  'I wanted to make this aRange, but coudn't get Range back from .Find
Dim bRange As Range
Dim b_Found As Boolean

'Define a flag for 'Text Found'
b_Found = False

'Use your own 'Find' parameters ... this is just what my macro recorded ...
aFound = ActiveSheet.Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
   
While Not (aFound Is Nothing)
    If Range(aFound).Value <> "A..." Then
        If b_Found Then
            MsgBox "ERROR PROCESS HERE - Multiple hits for 'A'"
            End
        Else
            'Save the Address of the Found Cell
            Set bRange = aRange
            b_Found = True
        End If
    End If
    'Use your own 'Find' parameters ... this is just what my macro recorded ...
    Set aRange = Cells.FindNext(After:=ActiveCell).Activate
Wend

'Show the address of the single found cell
MsgBox bRange.Address

End Sub

---

I can see your difficulty ... You really do need the Range object to be returned from the Function. Hopefully we can figure this one out.

Well, I don't know what changed, but I got the following code to work. Be advised that Excel '.Find' does something very secret and strange with the text string "..." (ellipsis)! Have fun with it! I changed it to "ABCD" for the following example:

Code:

---

Option Explicit
Sub Macro1()
Dim aRange As Range
Dim firstCell As Range
Dim bRange As Range
Dim b_Found As Boolean

'Define a flag for 'Text Found'
b_Found = False

'Use your own 'Find' parameters ... this is just what my macro recorded ...
Set aRange = ActiveSheet.Cells.Find(What:="A", after:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
'Save the 'First Found' for Wraparound test
Set firstCell = aRange

While Not (aRange Is Nothing)
   
    MsgBox aRange.Address  '<< TEST TEST TEST TEST
   
    If aRange.Text <> "ABCD" Then
        If b_Found Then
            MsgBox "ERROR PROCESS HERE - Multiple hits for 'A'" & Chr(10) & aRange.Address
            End
        Else
            'Save the Address of the Found Cell
            Set bRange = aRange
            b_Found = True
        End If
    End If
    'Use your own 'Find' parameters ... this is just what my macro recorded ...
    Set aRange = Cells.FindNext(after:=aRange)
    'Dont allow Wraparound
    If aRange.Address = firstCell.Address Then Set aRange = Nothing
Wend

'Show the address of the single found cell
If bRange Is Nothing Then
    MsgBox "Text 'A' was NOT found"
Else
    MsgBox bRange.Address
End If

End Sub

---

Hey guy I am trying figure some error handling code for .Find

Here is little part of my code

VB Code:

myaddy = Workbooks("emails.xls").Sheets("Sheet1").Range(EmplID & "1:" & EmplID & email_LastRow).Find(strTemp, LookIn:=xlValues).Address
 
Index = Split(myaddy, "$")(2)

However the macro crashes whenever value of strTemp is not found in emails.xls workbooks.

Can you guys help?

The "Find" function returns a Range of the search string IF it is found. If the search string is NOT found it returns a "Nothing". In this case you are trying to find the address of a "Nothing" which generates the error. Here is some code that might help you: (I put "test" or "texx" in a cell near the top of the test sheet)

Code:

---

Option Explicit
Sub Macro1()
Dim arange As Range

'FIND Returns a RANGE object
Set arange = ActiveWorkbook.ActiveSheet.UsedRange.Find("test")
If arange Is Nothing Then
    'The search string was NOT found and a "Nothing" was returned.
    MsgBox "The Search String was NOT found. Do NOT look for an Address!"
Else
    'The search string was found and a Range was returned.
    MsgBox "The Search String WAS found." & Chr(10) & Chr(10) & arange.Address
End If

End Sub

---

Thank you

Look at the Excel Tips and Tricks link in my sig for an example on Find. One of the parameters you might want to use is LookAt:=xlWhole (for exact match) or xlPart for partial match.

Also consider using the FindNext and FindPrevious methods to continue the Find. Be warned! This method is exactly the same as doing a Find through the menu, including setting of parameters, so it is very much worthwhile setting all the parameters when you run the Find through code or unexpected results may occur.

zaza