Replace Value with Formula

[StigPfau]

Hi everybody,

hope anybody can help.

I need a solution for an excel problem:

My sheet hat a lot of absolute values in a row e.g:

5, 3, 7, 6, 4, 4

I need to change this values into formulas like:

= Value (activecell) * value of a cell ($D$5)


My idea was something like that:

Sub test()
Dim Value As Variant
strFormula = "Value * $D$5"
ActiveCell.Formula = strFormula
End Sub

So the script has to read the active cell, modify the value an wite it in the same cell.

Thanks for you help

Stig

 

[Joe4]

Welcome to the Board!

This should be all that you need:

Sub test()
    ActiveCell.Formula = "=$D$5 * " & ActiveCell.Value
End Sub

 

[Trevor G]

The following code would loop through cells and replace the content by its content multiply against D2

Excel Workbook
	C	D
1	Check	Absolute
2	34	30
3	56
4	78
Sheet1

After the code

Excel Workbook
	C	D
1	Check	Absolute
2	1020	30
3	1680
4	2340
Sheet1

Code used

Sub loop12()
Range("c2").Select
Do Until ActiveCell.Value = ""
ActiveCell.Formula = "=" & ActiveCell.Value & "*$D$2"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[StigPfau]

Thanks for the great code. Works great.
Onnly one thing: if the fields are signed as cuurency, there is an error.

 

[Joe4]

Only one thing: if the fields are signed as currency, there is an error.

I do not see that with either solution provided.
Are you sure that your entries are numbers formatted as currency, and not text entries?
Because that WOULD cause an error, as the dollar sign would be a literal value in that cell.

 

[StigPfau]

Hi,

yes I double checked. The cells are number formatted with decimals.
Any idea - I need to fix the sheet.

 

[Joe4]

Please provide actual data examples you are working with, and post the exact VBA code you are currently using.