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?

 

[Oshiren]

Hello,

I am trying to multiply multiple tables with a factor. I want to be able change the factor so I like to keep the formula.

I tried the following:

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

But some how it changes the values in the cells instead of multiplying the values with B2.

i.e.:

B2 = 2

Values after macro	0	0	0	0
values before marco	1000	2000	3000	4000

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

I hope someone can help.

 

[pjmorris]

try this, which will cope with values and existing formulas:

Sub insertmultiplier()
    Dim cCell As Range
    For Each cCell In Selection
        If Left(cCell.Formula, 1) = "=" Then
            cCell.Formula = "=B2*" & Right(cCell.Formula, Len(cCell.Formula) - 1)
        Else
            cCell.Formula = "=B2*" & cCell.Formula
        End If
    Next cCell
End Sub

HTH

 

[Oshiren]

Peter,

Thank you it works fine, and looking at the code I see I have much to learn about VBA.

 

[pjmorris]

Hi Oshiren,

Firstly thanks for the feedback and delighted that it worked.

With regard to your last comment, I've seen far worse starting points for the code. A couple of specific points:

1. Dim cCell As Range it is simply good practice to formally declare variables. Better still is to set Option Explicit in the VBA preferences as this will force you to do so, which helps to avoid errors caused through inventing variables 'on-the-fly.'
2. Changed your cell variable to cCell since its not good practice to use variables with names of functions or objects (in this case I don't think cell is protected, but just seems to be a good idea).
3. This code "=B2*(" & Mid(cell.Formula, 2, 255) & ")" changed to "=B2*" & cCell.Formula" since without an '=' sign your code actually stripped off the initial digit (which is why you returned a 0 as the answers). The IF statement simply tests the left hand character of the cCell.Formula and if it is '=' removes it. Thinking about this now I think you could easily replace my Right(cCell.Formula, Len(cCell.Formula) - 1) with your Mid(cell.Formula, 2, 255).

Hope this helps.

 

[sheetspread]

your code actually stripped off the initial digit (which is why you returned a 0 as the answers)


Thinking about this now I think you could easily replace my Right(cCell.Formula, Len(cCell.Formula) - 1) with your Mid(cell.Formula, 2, 255).

Yes, the numbers 1000,2000,3000,4000 without the first digit become zero, which multiplied times anything equal zero. Peter's IF statement corrects for that (my previous one assumes an equal sign is already present), you can also write:

Sub insertmultiplier()
For Each ccell In Selection
ccell.Formula = "=B2*(" & Replace(ccell.Formula, "=", "") & ")"
Next ccell
End Sub

 

[pjmorris]

Sorry Sheetspread, I hadn't realised that you were the original poster of the macro - I'd assumed it was linked to the original thread, but not really linked to it!!.

Just a thought, but I'd always indent my macros - it makes them so much easier to read, helps avoid missing endif type errors and significantly improves debugging.

All the best.

 

[sheetspread]

I hadn't realised that you were the original poster of the macro

Yes, I had asked for what oshiren also wanted: cell ref * value, then years later someone asked for cell ref * formula, so I just wrote the first code that came to mind.

I'd always indent my macros - it makes them so much easier to read, helps avoid missing endif type errors and significantly improves debugging.

you're right!

Till next time.........