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

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

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

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

--
Regards,
Tom Ogilvy


"kurt" wrote in message
...
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

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

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)

"kurt" wrote in message
...
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

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

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

--
George Nicholson

Remove 'Junk' from return address.


"kurt" wrote in message
...
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