Excel VBA macro - using cell value in formula

Question

I have a repetitive task that I am trying to solve with a macro.

I have a cell with a number - let's say cell N4 holds 5782.3.

Now I would like to change the B4 cell content to =IF($K4<>0,5728.3,0)

How do i do it? I have tried the following:

 Dim a As Double
 a = ActiveCell.Value
 ActiveCell.FormulaR1C1 = "=IF(RC4<>0,a,0)"
 ActiveCell.Offset(1, 0).Range("A1").Select

But then I get in the cell IF($K4<>0,a,0) How should I write it?

something like this: Range("B4").Formula = "=IF($K4<>0," & Range("K4").Value & ",0)"? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 15, 2014 at 9:03

atkinchris · Accepted Answer · 2014-03-15 09:12:21Z

2

I don't think you need to use VBA for this.

If your variable a is the number in N4, you could just use the cell formula:

=IF($K4<>0,$N4,0)

The reason your VBA isn't working as expected is because a is inside quotes, and is treated as the character "a". Use:

 ActiveCell.FormulaR1C1 = "=IF(RC4<>0," & a & ",0)"

answered Mar 15, 2014 at 9:12

atkinchris

661 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1