Syntax error on text & formula in cell

[bluefuel318]

I know that this is a crazy-looking formula, but I am about to rip my hair out trying to figure out why I keep getting a syntax error when I try to run it in my macro.

<code> Selection.FormulaR1C1 = _
Selection.Value & "; due " &TEXT(VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C11,7,0),""MM/DD"")&"" from ""&INDEX('[OPEN POs.xlsm]Open Orders'!C1,MATCH(RC[10],'[OPEN POs.xlsm]Open Orders'!C5,0))&"" ""&VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C13,9,0)
</code>

I know that the Text formula on works, because this functions correctly:

<code>
Selection.FormulaR1C1 = _
"=""due ""&TEXT(VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C11,7,0),""MM/DD"")&"" from ""&INDEX('[OPEN POs.xlsm]Open Orders'!C1,MATCH(RC[10],'[OPEN POs.xlsm]Open Orders'!C5,0))&"" ""&VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C13,9,0)"
</code>

but I want the result of the formula appended to the current cell contents, and I just can't get that to work. I'm hoping it's just a misplaced quote or something.

Any help that anyone could give would be appreciated. Thank you!

 

[RickSchultz]

bluefuel,

It may not be the final answer, but I think you're missing a "=" at the beginning of your formula:

Selection.Value & "; due...

Or is the "=" already in your Selection.Value?

 

[bluefuel318]

It's there. I think the line break in the code is confusing, so I took it out:

<code>Selection.FormulaR1C1 = Selection.Value & "; due " &TEXT(VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C11,7,0),""MM/DD"")&"" from ""&INDEX('[OPEN POs.xlsm]Open Orders'!C1,MATCH(RC[10],'[OPEN POs.xlsm]Open Orders'!C5,0))&"" ""&VLOOKUP(RC[10],'[OPEN POs.xlsm]Open Orders'!C5:C13,9,0)</code>

Basically I just want the macro to add the formula result to the current cell's value. Is that possible without doing the formula in one cell and then doing:

<code>ActiveCell = ActiveCell.Value & [formula cell].Value</code> or something like that? That's what I'm trying to avoid.

 

[RickSchultz]

BlueFuel,

Since the part after "Text" appears to work, it's likely your error is in

Selection.FormulaR1C1 = Selection.Value & "; due " &TEXT

Whatever is in Selection.Value may be part of your problem; I can't comment on that. However, it looks like you have ; due in quotes, which means that the &TEXT part is NOT in quotes; since that's part of your formula, it needs to be in quotes.