How can I use the worksheet function FIND in VBA or is there a FIN

[Guest]

Hi everyone!

I'm trying to parse of a string from a cell in VBA.
I need to go thru one sheet "find" a sub-string within a cell and if it's
there copy that
row into a different sheet.

I tried using =IF(FIND("C2PC",G101),"yup","nope"), but I get an error.
I can't "find" a FIND in VBA.

Please help.
Regards, Kurt

 

[Tom Ogilvy]

Dim rng as Range
set rng = Cells.Find(What:="C2CP", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.EntireRow.copy Destination:=worksheets("Sheet2").Range("A2")
else
msgbox "Not found"
End if

 

[Bob Phillips]

Kurt,

You can use Instr

iPos = instr(Range("G101","C2PC")
If iPos > 0 Then
MsgBox "yup"
Else
MsgBox "nope"
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[George Nicholson]

FYI, FIND's semi-equivalent in VBA is Instr() but based on your description,
I think you want to use Tom Ogilvy's response.