multiply multiple cells by a single cell but retain formula

[sheetspread]

Lets say I want to highlight any cell or cells and multiply them all by the value in cell I7. Paste special multiply will leave me with the correct number but not the formula.

I7= 1.05

Say A4 = 100

Showing 105 is good but in the cell formula I'd like =100*1.05 to appear

Is there a VBA code or other technique that works?

 

[mvptomlinson]

Hi Sheet, you could use something like:

Range("A4").Formula = "=" & Range("A4").Value & "*" & Range("I7").Value

 

[the_original_invisible]

Or something like this?

Sub Macro1()
Dim cell As Range
iMultipler = Range("I7").Value
For Each cell In Selection.Cells
    icell = cell.Value
    Debug.Print icell
    cell.Formula = "=" & icell & "*" & iMultipler
Next
End Sub

Lee

 

[sheetspread]

Thanks guys!

roundabout, if I wish to modify the macro so that *I7 appears instead of *1.05, what should I type?

 

[sheetspread]

Forgive the bump,


Sub Macro1()
Dim cell As Range
iMultipler = Range("I7").Value
For Each cell In Selection.Cells
icell = cell.Value
Debug.Print icell
cell.Formula = "=" & icell & "*" & iMultipler
Next
End Sub

works great. As a variation, what should be done to change iMultipler = Range("I7").Value so that the result is *I7 instead of *(numerical content of I7)?

I tried iMultipler = "I7".cell and other layman inferences but no luck...........

 

[mikerickson]

cell.Formula = "=" & icell & "*" & Range("I7").Address(,,,True)

I gave it in this format rather than hard coding the I7 in case you want to use a different cell.

It might be easier in the long run if you had a helper column rather than replacing constant values with a formula.

 

[sheetspread]

Ah, brilliant Mike. Thank you. I'm actually using helper columns in other tabs that link to this summary sheet, and wanted a quick, different way to add one last variable adjustment.

 

[kristinka]

Hi everyone,
I hope I'm not committing anything against the rules by replying to this fairly old thread - it's just that the it basically describes my question - except I would need also the original formulas to stay and not be changed into values. I'm really bad at explaining, so please let me give an example...

[background: I have a fairly large table with formulas using input data from a different sheet and I would like to be able to multiply these by a "variable/adjustable" value without changing the original formulas]

simple example:
in C4 I have the following formula =Sheet2!A1*Sheet2!A2 (let's say the result is 40)
in C5 I have the following formula =Sheet2!A1+Sheet2!A2 (let's say the result is 13)
in A4 I have a "multiplier" (a number, let's say 10)

is there any way to create this (in C4, C5...): =A4*(Sheet2!A1*Sheet2!A2) and =A4*(Sheet2!A1+Sheet2!A2) without having to manually change the formulas (I do have A LOT)? Hope this makes sense.

It seems like something that *should* be easy to do but somehow I can't figure out how.

Thank you very much in advance.

K.

 

[sheetspread]

Select the cells you wish to change and run this:

Sub insertmultiplier()
For Each cell In Selection
cell.Formula = "=a4*(" & Mid(cell.Formula, 2, 255) & ")"
Next cell
End Sub

If it's easier use F5 (goto) to select special cells with formulas only first.

 

[kristinka]

Thank you so much, just what I needed!