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"
+ "=""FORECAST"",LEFT(
Can anyone suggest a solution (or a more elegant way of achieving my objectives)?
Thanks,
Gavona
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"
Can anyone suggest a solution (or a more elegant way of achieving my objectives)?
Thanks,
Gavona