Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating Excel formula in VBA 1

Status
Not open for further replies.

Gavona

Technical User
Aug 27, 2002
1,771
GB
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
 
Use the concatenation operator "&" instead of "+", and it will work just fine.


Rob
[flowerface]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top