Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change straight quotes to curly quotes | Excel Discussion (Misc queries) | |||
Ampersand in Header | Excel Discussion (Misc queries) | |||
ampersand in header | Excel Discussion (Misc queries) | |||
using ampersand in the header does not appear | Excel Discussion (Misc queries) |