Creating Excel formula in VBA 1

[Gavona]

I am trying to construct an excel formula within visual basic.
This works:
MyFormula = "=IF(RC16)" + "=""FORECAST"",LEFT……..
However:
The formulae need to refer to cells in up to 5 other columns but the position of those columns may change from time to time so I want to build a robust solution.
I can use Find and the column property to put the column number into a variable:
With the row containing my column headings selected
S6col = Cells.Find(What:="S6", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Column
But VBA gives a type mismatch with
MyFormula = "=IF(RC" + S6col + "=""FORECAST"",LEFT(
And doesn’t recognise the Text function
MyFormula = "=IF(RC" + Text(S6col, "0&quot + "=""FORECAST"",LEFT(

Can anyone suggest a solution (or a more elegant way of achieving my objectives)?

Thanks,
Gavona

 

[RobBroekhuis]

Use the concatenation operator "&" instead of "+", and it will work just fine.


Rob

 

[Gavona]

Thanks Rob, I really should have thought of that. I think I was misled by the fact that in my first example the + was interpreted as a concatenation operator. I guess there was no ambiguity with that expression though.

Thanks again

Gavona