Formula with quotes and ampersand
 

Hi!,

I am having trouble trying to enter this fromula in a cell using VBA, I keep
getting a message Type Mismatch.
I have tried Double quotes and also quotes around the ampersand but I am
still doing something wrong, I hope someone can help me out.

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

The quotes and ampersands are needed in the formula.


Thanks
Neil

Formula with quotes and ampersand
 

Neil,

Try

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("" * ""&$C3&"" * "",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil" wrote in message
...
Hi!,

I am having trouble trying to enter this fromula in a cell using VBA, I
keep
getting a message Type Mismatch.
I have tried Double quotes and also quotes around the ampersand but I am
still doing something wrong, I hope someone can help me out.

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

The quotes and ampersands are needed in the formula.


Thanks
Neil

Formula with quotes and ampersand
 

Just a heads up:

That produces =INDEX('[ENGINEER CODES.xls]Sheet1'!$G$3:$G$216,MATCH(" *
"&$C3&" * ",'[ENGINEER CODES.xls]Sheet1'!$F$3:$F$216,0))

so if C3 contained the characters BC, the item looked for would be " * BC *
" which I don't think was the original intent and wouldn't find ABCD for
example.


"=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH(""*""&$C3&""*"",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

produces:
=INDEX('[ENGINEER CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
Neil,

Try

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("" * ""&$C3&"" * "",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil" wrote in message
...
Hi!,

I am having trouble trying to enter this fromula in a cell using VBA, I
keep
getting a message Type Mismatch.
I have tried Double quotes and also quotes around the ampersand but I am
still doing something wrong, I hope someone can help me out.

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

The quotes and ampersands are needed in the formula.


Thanks
Neil

Formula with quotes and ampersand
 

Tom/Bob,

Thanks for your help, Tom's code was what I required. In the meantime I got
around it by putting the formula in another sheet and then just copied the
formula when it was required.

Once again thanks for your help.

Regards
Neil

"Tom Ogilvy" wrote in message
...
Just a heads up:

That produces =INDEX('[ENGINEER CODES.xls]Sheet1'!$G$3:$G$216,MATCH(" *
"&$C3&" * ",'[ENGINEER CODES.xls]Sheet1'!$F$3:$F$216,0))

so if C3 contained the characters BC, the item looked for would be " * BC
*
" which I don't think was the original intent and wouldn't find ABCD
for
example.


"=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH(""*""&$C3&""*"",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

produces:
=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
Neil,

Try

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("" * ""&$C3&"" * "",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil" wrote in message
...
Hi!,

I am having trouble trying to enter this fromula in a cell using VBA,
I
keep
getting a message Type Mismatch.
I have tried Double quotes and also quotes around the ampersand but I
am
still doing something wrong, I hope someone can help me out.

Range("I3").Formula = "=INDEX('[ENGINEER
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))"

The quotes and ampersands are needed in the formula.


Thanks
Neil