Problem with If formula and concatenate "&"
 

Very new to VBA.

I'm stumped. It seems very simple enough but I can't get it to work. I keep getting the Compile error: Expression expected

ActiveCell.Offset(0, 8).Formula = IF(ActiveCell.Offset(0, 5)="MC","M" & ActiveCell.Offset(0, 6),"V" & ActiveCell.Offset(0, 6))

I've tried adding quotation marks at the beginning and end of the formula but when I run the macro it only writes the formula, not the result.

Please help

Problem with If formula and concatenate "&"
 

AlGrrrr wrote:

Very new to VBA.

I'm stumped. It seems very simple enough but I can't get it to work. I
keep getting the Compile error: Expression expected

ActiveCell.Offset(0, 8).Formula = IF(ActiveCell.Offset(0, 5)="MC","M" &
ActiveCell.Offset(0, 6),"V" & ActiveCell.Offset(0, 6))

I've tried adding quotation marks at the beginning and end of the
formula but when I run the macro it only writes the formula, not the
result.

A few problems.

1. "IF" is not used in this manner. If it's part of the formula, it should be
contained within quotation marks and start with an equal sign, like "=IF
(...)".
1a. If "IF" is *not* part of the formula, then you should use IIf ("Immediate
If") instead.
2. You should use .Formula or .Value with the second, third, and fourth
ActiveCell.Offset.

Like so (untested but should work fine):
ActiveCell.Offset(0, 8).Value= IIf(ActiveCell.Offset(0, 5).Value="MC", _
"M" & ActiveCell.Offset(0, 6).Value,"V" & ActiveCell.Offset(0, 6).Value)

or:
ActiveCell.Offset(0, 8).Formula = IIf(ActiveCell.Offset(0, 5).Formula = _
"MC", "M" & ActiveCell.Offset(0, 6).Formula,"V" & _
ActiveCell.Offset(0, 6).Formula)

or:
ActiveCell.Offset(0, 8).Formula = "=IF(" & ActiveCell.Offset(0, 5).Value _
& "=""MC"",""M" & ActiveCell.Offset(0, 6).Value & "",""V" & _
ActiveCell.Offset(0, 6).Value) & """)"

--
Less complaining, more fleeing in terror!

Thanks a million!
The first two worked like a charm. I did not try the third option.

Just tried the third option. I still get the Compile error.