CODEWITHSUNDEEP
excelvbareferencecell
Slash
Slash

Reputation: 509

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

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.

Upvotes: 0

Views: 307

Answers (2)

QHarr
QHarr

Reputation: 84465

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"

Upvotes: 1

skulden
skulden

Reputation: 423

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"

Upvotes: 0

Related Questions