Equivalent VBA for MID/FIND formula

[jwb96]

I'm stumped. I have a mid-find formula that I can create on a
worksheet [=MID(B2,FIND("categoryId=",B2)+11,5)] that I can't get to do
what I want in VBA. What I need VBA to do is use this formula but use
a range variable for a cell I've already DIM'd and SET instead of B2.

Or, is there a way to turn the above forumula into a VNA function? MID
works the same in VBA and Excel, but I can't find what in VBA works the
same as Excel's FIND. VBA's find is like the Ctrl-F find capabilities.
TIA,
Jim

 

[Guest]

I had the same problem and someone helped me out.
It's hard to 'find', but it's the instr function that you can get to work
the same way as a find. x=instr(a,lookin,lookfor,kindofcompare)
a is the start position in lookin
x= where it was found.

 

[Bob Phillips]

or

myVar = Ecaluate("[=MID(" & rng.Address(False,False) &
",FIND("categoryId=",(" & rng.Address(False,False) & ")+11,5)")

--
HTH

Bob Phillips

I had the same problem and someone helped me out.
It's hard to 'find', but it's the instr function that you can get to work
the same way as a find. x=instr(a,lookin,lookfor,kindofcompare)
a is the start position in lookin
x= where it was found.

I'm stumped. I have a mid-find formula that I can create on a
worksheet [=MID(B2,FIND("categoryId=",B2)+11,5)] that I can't get to do
what I want in VBA. What I need VBA to do is use this formula but use
a range variable for a cell I've already DIM'd and SET instead of B2.

Or, is there a way to turn the above forumula into a VNA function? MID
works the same in VBA and Excel, but I can't find what in VBA works the
same as Excel's FIND. VBA's find is like the Ctrl-F find capabilities.
TIA,
Jim

 

[Guest]

The InStr function in vba is similar to Find() in Excel, but you can make use
of an Excel worksheet function in vba as a member of the
application.worksheetfunction collection. For example;

Application.WorksheetFunction.Find(myText,myString)

would return the location of the value of myText in myString just as if in
Excel.

Hope that helps.

Tom

 

[Tom Ogilvy]

Just to reduce the possibility of confusion from a typo,

Ecaluate
should be
Evaluate

--
Regards,
Tom Ogilvy

or

myVar = Ecaluate("[=MID(" & rng.Address(False,False) &
",FIND("categoryId=",(" & rng.Address(False,False) & ")+11,5)")

--
HTH

Bob Phillips

I had the same problem and someone helped me out.
It's hard to 'find', but it's the instr function that you can get to work
the same way as a find. x=instr(a,lookin,lookfor,kindofcompare)
a is the start position in lookin
x= where it was found.

I'm stumped. I have a mid-find formula that I can create on a
worksheet [=MID(B2,FIND("categoryId=",B2)+11,5)] that I can't get to do
what I want in VBA. What I need VBA to do is use this formula but use
a range variable for a cell I've already DIM'd and SET instead of B2.

Or, is there a way to turn the above forumula into a VNA function? MID
works the same in VBA and Excel, but I can't find what in VBA works the
same as Excel's FIND. VBA's find is like the Ctrl-F find capabilities.
TIA,
Jim