vlookup issue

[lotsasmiles]

I'm not sure if my post actually went thru, so I'm posting again. When trying to assign a cell value to a vlookup formula, it keeps adding (')s around part of my variable. I keep track of the worksheet I want to connect to (within the same workbook), and it's formatted like Month Year (ie: September 2007). So when I use the vba code of:

workingCell.Offset(1, 0).Value = "=IF(ISERROR(VLOOKUP(B9," & MonthName(month(targStName)) & " " & Year(targStName) & "!$B$9:$L$23,11,0)),"""",VLOOKUP(B9," & targStName & "!$B$9:$L$23,11,0))"

(trying two different ways), it appears in the cell as:
=IF(ISERROR(VLOOKUP(B9,October '2007'!$B$9:$L$23,11,0)),"",VLOOKUP(B9,October '2007'!$B$9:$L$23,11,0))
Notice the (')s around the year. How do I get rid of that as it's screwing things up? Any suggestions would be appreciated. Thanks!

 

[BrianB]

You have a space in the worksheet name. Excel "helpfully" is trying to figure out what you want.

You need to put your own apostrophe "'" in the right place at the beginnig and end so it looks like
='[Book1]October 2007'!$A$1
eg

' not code really, but hopefully shows the characters better
"'" &"[Book1]October 2007" &"'!"

... etc

Personally I would use extra columns to calculate the strings and use reference to those in the VLOOKUP() - or more likely - use Find instead.