CODEWITHSUNDEEP
vbaexcel
Andrew Perry
Andrew Perry

Reputation: 765

How do I set a formula in a cell using R1C1 notation from variables?

I've got a series of IF/ELSE clauses to see if items fit into one of three categories. If an item doesn't fit into any of them, its category field should simply read "SELECT".

Now, with simple text strings it works fine. But I also want to include a simple formula (as in most of the other autopopulated fields in the sheet) to blank the field if it's on an empty line. Based on the code I've used in other cases, I would want to set the cell's formula to

=IF(ISBLANK($A2, "", "SELECT")

(...where the cell itself is B2)

Problem is, the cell to look at will depend on which line it's on; A2, A3, A4 and so on. The column number remains constant, but the row number is defined by the variable iImportCounter.

I've tried both .Value and .Formula, both """ and Chr(34), and it still doesn't seem to work. The full code for that line as it stands is

Else:
    wsCalc.Cells(iImportCounter, 3).Formula = "=IF(ISBLANK($" & Application.ConvertFormula(Cells(iImportCounter, 2), xlA1) & ", " & Chr(34) & Chr(34) & ", " & Chr(34) & "SELECT" & Chr(34) & ")"

Is there something terribly obvious I'm missing?

I've seen comments elsewhere that the fact that it's in a module rather than a sheet-based macro could make some of that code malfunction, but I'm not sure what or how.

Upvotes: 0

Views: 2174

Answers (2)

user4039065
user4039065

Reputation:

It depends on what the iImportCounter starts at. If it is 3 (row 3 in B3) then the formula would be,

with wsCalc.Cells(iImportCounter, 3)
    .FormulaR1C1 = "=IF(ISBLANK(R[-1]C1), """", ""SELECT"")
end with

The R in RC1 means the same row that you are putting the formula on so if you are putting on row 2 and you want to reference A2 then R is all you need. If the formula is going into B3 then you need to reference the current row minus 1 with R[-1]C1.

The C1 in RC1 means a 'locked' (absolute) column A (the first column). This doesn't seem absolutely necessary as the formula does not travel laterally.

Upvotes: 3

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Assuming wsCalc is a reference to a range of cells:

wscalc.FormulaR1C1 = "=IF(ISBLANK(RC1),"""",""SELECT"")"

As a working example:

Sub Test()

    ThisWorkbook.Worksheets("Sheet1").Range("B1:B10").FormulaR1C1 = "=IF(ISBLANK(RC1),"""",""SELECT"")"

End Sub

Upvotes: 1

Related Questions