Value of Range equal to previous range's value and address VBA

Question

I want to replace A1's value, 4, with =4*A2. However, when I make an attempt:

Workbooks("Personal.xlsb").Worksheets("Sheet1").Activate
Range("A1").Value = "=" & Range("A1").Value * Range("A2").Address(0, 0)

I get a mismatch error. Running this:

Range("A1").Value = "=" & Range("A1").Value * Range("A2").Value

returns a value of 16 (A2=4), but as stated before I would like A1 to be 4*A2. Any suggestions? Thank you very much for your help.

EDIT: I've realized I need something different. I'd like to replace a range of cells with their previous values and a cell's reference. The part where I am now running into an error is replacing a range of cells with their previous values, I still receive a mismatch error.

oldvalue = Range("D8:I8").Value
Range("D8:I8").Formula = "=" & oldvalue & "*A2"

Any ideas? thanks again for your help.

I am able to do this with a For Each loop:

Sub test()
Workbooks("Personal.xlsb").Worksheets("Sheet1").Activate
Dim rng As Range: Set rng = 
Workbooks("Personal.xlsb").Worksheets("Sheet1").Range("D8:I8")
Dim cel As Range
For Each cel In rng.Cells
    With cel
        oldvalue = .Value
        .Formula = "=a2*" & oldvalue
    End With
Next cel
End Sub

However if anyone can think of a way to do this without a loop that'd be even better.

Are you setting up a circular reference? And is 4 actually a constant or a variable? And you potentially want .Formula property. — QHarr, Jul 30 '18 at 20:17
Yes, four is a constant and I am not setting up a circular reference when I run this. — Slash, Jul 31 '18 at 12:56
Then [A1].FormulaR1C1 = "=4*R[1]C" would work fine as uses constant. — QHarr, Jul 31 '18 at 13:03
Does this answer your question? [How do I get the old value of a changed cell in Excel VBA?](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) — Janine White, Feb 08 '22 at 21:43

QHarr · Accepted Answer · 2018-07-30T21:23:36.827

1

You can use

[A1].FormulaR1C1 = "=4*R[1]C"

If you needed the old value of A1 then you could indeed store in a variable

Dim iVal As Double
iVal = [A1].Value
[A1].FormulaR1C1 = "=" & iVal &  "*R[1]C"

edited Jul 30 '18 at 21:23

answered Jul 30 '18 at 20:19

QHarr

83,427
12
54
101

score 0 · Answer 2 · answered Jul 30 '18 at 20:38

The better way to write fórmulas is using the .FormulaLocal.

For your case i recomend use a variable to keep the old value of range A1 too.

Case example:

Workbooks("Personal.xlsb").Worksheets("Sheet1").Activate
oldvalue = Range("A1").value
Range("A1").FormulaLocal = "=" & oldvalue & "*A2"

Value of Range equal to previous range's value and address VBA

2 Answers2