Automatic Multiplication

[mickyd67]

Hi all - I couldn't find anything on Google so coming to somewhere much better

I am looking to understand if this is possible:

I enter the number 10 (let's say) into Cell A1 and that automatically returns a value of 50, because the cell is set up to multiply any number you type x5.

I'm trying to avoid using the formula of 10*5 because I have to adjust the number 10 multiple times across the file but I want all the cells to multiply by 5 when I update them, it's just taking ages to go in and adjust each formula.

Hope that makes sense...

 

[Joe4]

You can use VBA code to automatically multiply any manual entry you make by 5.
If you let us know exactly what range of cells you want this behavior to apply to, we can help you come up with that VBA code.

 

[mickyd67]

Hi Joe - many thanks for the reply, and the offer to help.

I would have 2 ranges it'd would apply to (but I assume I can add more if I know the VBA code and how it works)

In this instance:

Multiplication value: Cell A2 / Applies to Range: B2 to M31 (basically 12 months across, x30 rows)

And also:

Multiplication value: Cell A33 / Range: B33 to M62

Hope that is what you're after.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the following VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range
    Dim rng2 As Range
    Dim cell As Range
    
'   See if any cells updated in first range
    Set rng1 = Intersect(Target, Range("B2:M31"))
    
'   Update cells if value updated in rng1
    If Not rng1 Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng1
            cell.Value = cell.Value * Range("A2").Value
        Next cell
        Application.EnableEvents = True
    End If
    
'   See if any cells updated in first range
    Set rng2 = Intersect(Target, Range("B33:M62"))
    
'   Update cells if value updated in rng1
    If Not rng2 Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng2
            cell.Value = cell.Value * Range("A3").Value
        Next cell
        Application.EnableEvents = True
    End If
 
End Sub

This code will automatically run/update when you manually enter values in B2:M31 or B33:M62.

 

[mickyd67]

That works a treat - many thanks Joe.

The task I am doing is still super painful mind you so I am going to start a new thread to see if the Solver function can help.

 

[Joe4]

You are welcome.