Using VBA commands inside an excel spreadsheet?

1
votes

can you, as a method to select a cell in an excel formula put in VBA commands?

FOR EXAMPLE:

ActiveCell.FormulaR1C1 = _
    "=IF(**Range(NamedRange).Cells(1,1)**=""Please add a title"",0,RC[-1]*VLOOKUP(R13C1,'Tables (H)'!R2C8:R6C10,2,FALSE))"

So in other words I want to use the first cell of the named range to be referenced in my excel formula

2
vbaexcel

2 Answers

2
votes

Yes you can. After all it's just a string that you construct.

UNTESTED

Dim sFormula As String

'=IF(A13="Please add a title",0,B17*VLOOKUP(A13,'Tables (H)'!$H$2:$J$6,2,FALSE))
sFormula = "=IF(" & Range(NamedRange).Cells(1, 1).Address & _
           "=""Please add a title"",0,B17*VLOOKUP(" & _
           Range(NamedRange).Cells(1, 1).Address & _
           ",'Tables (H)'!$H$2:$J$6,2,FALSE))"

ActiveCell.Formula = sFormula
0
votes

You can use any VBA function you created in a formula.

Then, create a "function" that does what you intend and call it in your Sheets where needed.