Equivalent VBA for MID/FIND formula

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

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.

"jwb96@hotmail.com" wrote:

> 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
>
>

or

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

--
HTH

Bob Phillips

"Neal Zimm" <nealzimm@yahoo.com> wrote in message
news:78326C06-B9D5-4EC2-B550-54C9C2C478BA@microsoft.com...
> 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.
>
> "jwb96@hotmail.com" wrote:
>
> > 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
> >
> >

Just to reduce the possibility of confusion from a typo,

Ecaluate
should be
Evaluate

--
Regards,
Tom Ogilvy

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:ulI$qdX$EHA.3700@tk2msftngp13.phx.gbl...
> or
>
> myVar = Ecaluate("[=MID(" & rng.Address(False,False) &
> ",FIND("categoryId=",(" & rng.Address(False,False) & ")+11,5)")
>
> --
> HTH
>
> Bob Phillips
>
> "Neal Zimm" <nealzimm@yahoo.com> wrote in message
> news:78326C06-B9D5-4EC2-B550-54C9C2C478BA@microsoft.com...
> > 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.
> >
> > "jwb96@hotmail.com" wrote:
> >
> > > 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
> > >
> > >
>
>

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

"jwb96@hotmail.com" wrote:

> 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
>
>