Active VBA - Multiply input with data in cell range

[Masimo85]

Hi,

So I've tried to work with an active macro here to multiply input into Cell range A11-A18 with the cell value of A5

However, I need the same macro to do it for all cells in the same numeric value but different Alphabetic value if that makes sense.

Basically, I need it to do the same for row B11-B18 with the cell value of B5 - etc for a wide range in the Alphabetic value.



Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E11:E18")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * Range("E5").Value
Application.EnableEvents = True
End Sub

Can anyone help me here?

 

[Joe4]

What should trigger this code to run?
A manual entry into row 5?

 

[Masimo85]

Right now it works when I input data into rows A11-A18 - and then it Multiply the entry with value of A5.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
    
    If Intersect(Target, Rows("11:18")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value * Cells(5, Target.Column).Value
    Application.EnableEvents = True
    
End Sub

 

[Masimo85]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
    If Intersect(Target, Rows("11:18")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value * Cells(5, Target.Column).Value
    Application.EnableEvents = True
   
End Sub

That works, thank you Joe4.

 

[Joe4]

You are welcome.

 

[Masimo85]

Hei Joe4 - or anyone else able to help with this one again.

So everything worked flawlessly, until i did some formatting of the cells I used.
I thought that it was only visuell effects that would change the cells, however the "Active Macro" to multiply the input with Cell 5 is not working anymore?

I dno if this works, however heres a smaller clipout of the Sheet - its in norwegian, so disregard the text

Prisbase Element - TEST.xlsm
	A	B	C	D
1	Post	1	2	3
2	Prosjekt Nummer
3	Element no.
4	Antall vinduer
5	Antall personer	2
6	H
7	B
8	Areal	0	0	0
9
10	Forbruk i timer:
11	Stenderverk	1,00
12	Gips	1,00
13	Vindu	1,00
14	Lekt	1,00
15	Kledning	1,00
16	Isolasjon	1,00
17	Plast	1,00
18	Inv. Lekter	1,00
MAL - VEGG

Cell Formulas
Range	Formula
B8:D8	B8	=B7*B6

 

[Joe4]

I thought that it was only visuell effects that would change the cells, however the "Active Macro" to multiply the input with Cell 5 is not working anymore?

In the example you posted, their is nothing in cell C5. So, there is nothing to multiply by.

Can you post an actual example that is not working, and walk us through the steps that don't work (i.e. what number are you placing in what cell)?

 

[Masimo85]

In the example you posted, their is nothing in cell C5. So, there is nothing to multiply by.

Can you post an actual example that is not working, and walk us through the steps that don't work (i.e. what number are you placing in what cell)?

Hei Joe4 ..

Well the macro is working by multiplying the input data in cell 5 of the current Target.Column.

I belive thats how we made it work in the Macro?
Its not bound to C5.. ?

As Ive done in the current example the input is in B5 and the row B11-B18 should be multiplied by "2"?

 

[Joe4]

Well the macro is working by multiplying the input data in cell 5 of the current Target.Column.

I belive thats how we made it work in the Macro?
Its not bound to C5.. ?

As Ive done in the current example the input is in B5 and the row B11-B18 should be multiplied by "2"?

The way the code is written, any time you manually enter a value in any one cell in rows 11 through 18, it will automatically multiply that number by the value in row 5 of that same column.
So, if if make an entry in cell B14, it will multiply it by the value in cell B5.
It is my understanding that is what you want to happen, right?

If there is some situation that is not working out for you, please walk us through an example, in detail, step-by-step.
What value are you entering in what cell?
What is the value in row 5 of that same column?
Is it not multiplying the values together for you?