Excel VBA Add formula based on row

Question

So I have that table above, I use Excel VBA to add new prices then add the formula to Decision column.

As you can see, cell B2 formula should be =IF($A2>50000,"Ignore","Buy") and cell B3 formula should be =IF($A3>50000,"Ignore","Buy") so the formula in B2 refers to the value in A2, this is the same for B3 to A3 and so on. I use the VBA below to add the same formula to blank cells. Yes, there will be blank decision cells and they need formula. I must NOT use autofill from top to bottom. I tried using below (LastRow is the usedrange.row):

Sheet1.Range("B2:B" & LastRow).SpecialCells(xlCellTypeBlanks). _ 
Formula = "=IF($A2>50000,""Ignore"",""Buy"")"

The problem with that VBA is even in cell B5 the formula is =IF($A2>50000,""Ignore"",""Buy"") when it should be =IF($A5>50000,""Ignore"",""Buy"") (should be $A5 instead of $A2). What am I doing wrong?

Answer 1

With SpecialCells(xlCellTypeBlanks) you will probably get a non continuous range. With this the auto fill process will not work with A1 formulas. But with R1C1formulas it will.

Use:

.Range("B2:B" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC1>50000,""Ignore"",""Buy"")"

RC1 means the Row you are currently in but always fix Column 1.

For R1C1 references see https://support.office.com/en-us/article/Overview-of-formulas-7abfda78-eff3-4cc6-b4a7-6350d512d2dc?CorrelationId=2bedf5ef-a3b7-4a82-9b12-6ee86b494ae9&ui=en-US&rs=en-US&ad=US#bmusing_references_in_formulas. Scroll down to The R1C1 reference style.

Answer 2

You can paste the formula in all cells, considering you have the formula in cell "B2":

Range("B2").Copy
Range("B2:B"  & LastRow).PasteSpecial xlPasteFormulas

Answer 3

edit for more detail: You can use the R1C1 reference style, more importantly, R[1]C[1] notation. There is a caveat for different languages though, see the very end of the post. Examples:

R2C4       'row 2, column 4 so it's the cell D2 in A1-notation
R[2]C[4]   'the cell 2 to the right and 4 down from the current cell (where this reference is located)
R[2]C4     'the cell 2 to the right from the current cell in column 4 (D)
R[-2]C[-4] 'you can also give negative arguments, this is the cell 2 to the left and 4 up
R[2]C      'the same as R[2]C[0]
RC[4]      'the same as R[0]C[4]
R2C        'the same as R2C[0]
RC4        'the same as R[0]C4
R2         'row 2
C4         'column 4 (the same as D:D)

As you can see from the last three examples, the notations can't be mixed.

Now for your case:
If you want to have the following in cell Bx (replace x by any number)

"=IF($Ax>50000,""Ignore"",""Buy"")"

This would be the R1C1 formula

"=IF(RC1>50000,""Ignore"",""Buy"")"

or if it is more important that it is the column to the left:

"=IF(RC[-1]>50000,""Ignore"",""Buy"")"

The latter would be the like dropping the $ from the original formula.

Your second formula was

"=IFERROR(VLOOKUP(RC3,Database!$A:$F,3,FALSE),""Missing"")"

and Axel's answer

"=IFERROR(VLOOKUP(RC3,Database!C1:C6,3,FALSE),""Missing"")"

should be clear now.

If you don't want or can't use the formulaR1C1 property but still use the R1C1 style reference for a single cell, you can use the INDIRECT worksheet function. INDIRECT("R1C1",FALSE) is a reference to R1C1. The FALSE tells it to use R1C1 instead of A1 notation. It might behave slightly different than a simple reference if there is something other than numbers in the referenced cell.

I personally like the R1C1 notation better than the A1 notation mostly because it is easier to reference cells relative to the current position but also because it is easier to read for high column numbers and it's closer to the Cells(rowIndex,columnIndex) syntax.

One last thing: In other language versions of excel, R1C1 might be named differently. That doesn't affect the formula when you enter it via VBA (I think) but if you want to enter it from the worksheet, you need to keep that in mind. In German it's Z1S1 for example. This can also cause problems when opening the file with a different language version. If you used INDIRECT("R1C1",FALSE) in a formula, the INDIRECT and FALSE will be translated but the string will not so it will not work :( (The last part is from memory)