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?

 

[Masimo85]

Hi again Joe4.

As mentioned earlier that the code worked as intenden, and as you describe it up until I formatet the visuell apperance of the cells.
What I guess I could have done wrong here is that I used the style for formatting the cells - I dno if that changes the way the Macro should work.

Either way ill explain in detail what I need it to do, and what dosnt work anymore:

The Excel ark is to calculate time spend during certain operations in our production line so -

1. Rows B2-B8 is project based data, those are the data that I input first.
In this example I only input in cell B5 - 2.
So if the Macro is working, it should multiply the input data with x2

Prisbase Element - TEST.xlsm
	A	B
1	Post	1
2	Prosjekt Nummer	0
3	Element no.	0
4	Antall vinduer	0
5	Antall personer	2
6	H	0
7	B	0
8	Areal	0
MAL - VEGG

Cell Formulas
Range	Formula
B8	B8	=B7*B6

2. Rows B11-B18 - are cells that contain the time spend during those operations.
In this example i typed in values from 1-8 in those cells - non of them gets multiplied by x2?

Prisbase Element - TEST.xlsm
	A	B
10	Forbruk i timer:
11	Stenderverk	1,00
12	Gips	2,00
13	Vindu	3,00
14	Lekt	4,00
15	Kledning	5,00
16	Isolasjon	6,00
17	Plast	7,00
18	Inv. Lekter	8,00
MAL - VEGG

3. Reasons for the macro here - is so that it multiplies the time spend on the operations with B5. That cell contains how many people are working on the element this could be anywhere to 2-5 people.

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

Cell Formulas
Range	Formula
B8	B8	=B7*B6

 

[Joe4]

OK, a few things:
1. This macro only fires when something in rows 11-18 are entered. It does not run when you make an entry in row 5. So the entry in row 5 must happen BEFORE you enter the values in rows 11-18.
2. I am not sure what formats you are applying, but you need to be careful and make sure that you are not changing the formats of any of these cells to text, as they need to be numeric.
3. Do you have any other VBA code running? If something is causing events to be disabled, then the code would not fire anymore.

Regarding the 3rd item, there is an easy way to check. Add a few message boxes to the code to return something to the screen, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Code is running"
    If Target.CountLarge > 1 Then Exit Sub
   
    If Intersect(Target, Rows("11:18")) Is Nothing Then Exit Sub
    MsgBox "Condition met"
    Application.EnableEvents = False
    Target.Value = Target.Value * Cells(5, Target.Column).Value
    Application.EnableEvents = True
   
End Sub

So try adding those rows in there, and then try the step that is not working.
Do you get either one (or both) of those message boxes to pop-up?
If not, something is disabling the events, thus causing your code not to fire anymore.
You can turn events back on by manually running this code:

Sub EnableEvents()
    Application.EnableEvents = True
End Sub

Lastly, regarding step 2, if the applying of a formatting style is causing it to stop, it may have changed one of the data types.
If you. say entered a value in cll B14 and it does not work, then enter these two formulas in any blank cells and tell us what they return:
=Type(B5)
=Type(B14)

 

[Masimo85]

OK, a few things:
1. This macro only fires when something in rows 11-18 are entered. It does not run when you make an entry in row 5. So the entry in row 5 must happen BEFORE you enter the values in rows 11-18.
2. I am not sure what formats you are applying, but you need to be careful and make sure that you are not changing the formats of any of these cells to text, as they need to be numeric.
3. Do you have any other VBA code running? If something is causing events to be disabled, then the code would not fire anymore.

Regarding the 3rd item, there is an easy way to check. Add a few message boxes to the code to return something to the screen, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Code is running"
    If Target.CountLarge > 1 Then Exit Sub
  
    If Intersect(Target, Rows("11:18")) Is Nothing Then Exit Sub
    MsgBox "Condition met"
    Application.EnableEvents = False
    Target.Value = Target.Value * Cells(5, Target.Column).Value
    Application.EnableEvents = True
  
End Sub

So try adding those rows in there, and then try the step that is not working.
Do you get either one (or both) of those message boxes to pop-up?
If not, something is disabling the events, thus causing your code not to fire anymore.
You can turn events back on by manually running this code:

Sub EnableEvents()
    Application.EnableEvents = True
End Sub

Lastly, regarding step 2, if the applying of a formatting style is causing it to stop, it may have changed one of the data types.
If you. say entered a value in cll B14 and it does not work, then enter these two formulas in any blank cells and tell us what they return:
=Type(B5)
=Type(B14)

Hi Joe4,

So after alot of back and forth I found the issue.

Apperantly if I mistype anything in the cells that are affected with the Macro - I get the Debug warning - I assumed by just stopping the debugger and returning to the sheet that I could continue.
I guess this happened at a time during the formatting - and the only solution to get the macro to work again is to close down the workbook and start it up again.

Again thank you so much for your time and inputs

 

[Joe4]

Apperantly if I mistype anything in the cells that are affected with the Macro - I get the Debug warning - I assumed by just stopping the debugger and returning to the sheet that I could continue.

No, if that is what causes it, I can tell you exactly what the issue is.

You need to understand what this line is doing:

    Application.EnableEvents = False

What this does is temporarily disable all automated VBA code from running while we are making updates to the cell.
Here is the reason why. The "Worksheet_Change" event procedure runs automatically when we change a cell value. However, in your code, we are updating the value in a cell.
So what would happen is that change would "trigger" the "Worksheet_Change" code to run again (since it is a change to the value), and you would get caught in an infinite loop.
So we tell it to temporarily suspend automated triggered code from running while we make that change.
Then, when the change is complete, we turn that back on with this line:

    Application.EnableEvents = True

However, if you exit the code AFTER we have turned that OFF, but BEFORE be turn it back on, automated code is shut off, and won't trigger anymore.
Closing Excel and re-opening will reset that setting. Or you could just run the "EnableEvents" code I gave you in my previous post.

Typically, the best way to handle this is to add some code to your procedure that makes sure that we can perform the procedure before continuing, so you don't need to exit the code.
I am assuming that it is a non-numeric entry that is causing options. If so, we can amend the code like this, and I think that should solve your issue:

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

    If IsNumeric(Target) And IsNumeric(Cells(5, Target.Column)) Then
        Application.EnableEvents = False
        Target.Value = Target.Value * Cells(5, Target.Column).Value
        Application.EnableEvents = True
    End If
  
End Sub

Now this code will only run if the cell updated, and the value in row 5 of that same column are both numeric (so we can perform the mathematical operation without error).

 

[Masimo85]

No, if that is what causes it, I can tell you exactly what the issue is.

You need to understand what this line is doing:

    Application.EnableEvents = False

What this does is temporarily disable all automated VBA code from running while we are making updates to the cell.
Here is the reason why. The "Worksheet_Change" event procedure runs automatically when we change a cell value. However, in your code, we are updating the value in a cell.
So what would happen is that change would "trigger" the "Worksheet_Change" code to run again (since it is a change to the value), and you would get caught in an infinite loop.
So we tell it to temporarily suspend automated triggered code from running while we make that change.
Then, when the change is complete, we turn that back on with this line:

    Application.EnableEvents = True

However, if you exit the code AFTER we have turned that OFF, but BEFORE be turn it back on, automated code is shut off, and won't trigger anymore.
Closing Excel and re-opening will reset that setting. Or you could just run the "EnableEvents" code I gave you in my previous post.

Typically, the best way to handle this is to add some code to your procedure that makes sure that we can perform the procedure before continuing, so you don't need to exit the code.
I am assuming that it is a non-numeric entry that is causing options. If so, we can amend the code like this, and I think that should solve your issue:

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

    If IsNumeric(Target) And IsNumeric(Cells(5, Target.Column)) Then
        Application.EnableEvents = False
        Target.Value = Target.Value * Cells(5, Target.Column).Value
        Application.EnableEvents = True
    End If
 
End Sub

Now this code will only run if the cell updated, and the value in row 5 of that same column are both numeric (so we can perform the mathematical operation without error).

You sir, are a legend... Thank you so much

 

[Joe4]

You are welcome.