Formula with quotes and ampersand

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

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
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
CODES.xls]Sheet1'!$G$3:$G$216,MATCH("*"&$C3&"*",'[ENGINEER
CODES.xls]Sheet1'!$F$3:$F$216,0))

--
Regards,
Tom Ogilvy


Bob Phillips said:
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 said:
Hi!,

I am having trouble trying to enter this fromula in a cell using VBA,
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top