vba to find cells with specific text and call a macro

[vijayamani1987]

Hi,

I am trying a macro to find a text in any cell at column (D) and activate that cell and call a macro. The following macro works only for first result. How to make it to continue search for the remaining cells in column D.

Sub CloseTest()
With Worksheets("Ledger").Range("D8:D500")
Set c = .Find("Close Now", LookIn:=xlValues).Activate
Call CLoseOrder
End With
End Sub


Thanks in advance.

 

[Rick Rothstein]

The help files do not mention it, but my recollection is that if you use the Call keyword, you must include parentheses for the argument list even when there is no argument list required by the subroutine. See if using this works for you...

Call CloseOrder()

 

[vijayamani1987]

Hi Rick Rothstein ,

That's not a problem. The above vba works only for the first result. I want it to keep search for the remaining cells in column D.

 

[Rick Rothstein]

That's not a problem. The above vba works only for the first result. I want it to keep search for the remaining cells in column D.

I guess my recollection was wrong then (I never use the Call keyword when I reach out to other subroutines). Anyway, this should do what you want (the code came from the help files for the Find method for a range)...

[table="width: 500"]
[tr]
	[td]Sub CloseTest()
  Dim C As Range, FirstAddr As String
  With Worksheets("Ledger").Range("D8:D500")
    [B][COLOR="#0000FF"]Set C = .Find("Close Now", LookIn:=xlValues)[/COLOR][/B]
    If Not C Is Nothing Then
      FirstAddr = C.Address
      Do
        Call CloseOrder
        Set C = .FindNext(C)
      Loop While Not C Is Nothing And C.Address <> FirstAddr
    End If
  End With
End Sub[/td]
[/tr]
[/table]

On caveat though... the Find method for a range "memorizes" some of its arguments for use the next time the Find method is called in code OR from when Excel's Find dialog box was last used. These memorized arguments can cause problems when the Find method is called in code without specifically specifying them. Given that, it would be better if the code line highlighted in blue above specified all of the argument settings instead of just the What and LookIn arguments. You can check out the help files for Range.Find to see what those arguments are and what their settings can be.