Worksheet_Change multiple ranges and different formulas

[RAECH]

Hi everyone

I am building a new file for my company and I find myself in need of multiple Worksheet_Change one one of my sheets.

The file consist of multiple months, and for each month we are entering a value and we would like it to calculate a value based imputed in the previous cell. And vica versa.

Some content: We recalculate always from $ to our cent/Kg or maybe we have a target Cent/kg we then calculates to full $-value.

Example of my sheet.



KG, TONNES
92,349






$ 1,000
Cent/KG


800,000
866


78,005
84



We have the above for full 12 months.

My code now for the first column to create a formula that is $/KG*100.
Which works when we input a value in our $-column. But we also need to opportunity to directly write the Cent/KG, and then automatically calculate back to $.

I know how to write to two codes seperately - but my issue is to combine multiple "Worksheet_Change"..
I tried to use "If Target.Column = xx Then" "Else" but with no luck.



Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("P24:P31", "P33:P34")) Is Nothing Then

Target.Offset(0, 0).Activate
Dim acc_mth1 As Range
Set acc_mth1 = ActiveCell

Application.ScreenUpdating = False

If IsNumeric(acc_mth1.Value) Then
acc_mth1.Offset(0, 1).Formula = "=" & acc_mth1.Address & "/$P$19*100"

End If

If acc_mth1.Value = "" Then
acc_mth1.Offset(0, 1).ClearContents
End If
End If


End Sub

Any tips for how to solve this best?

Best regards,
Rasmus

[Paul_Hossler]

An example workbook with the 12 months (?) and 2 (?) data columns would help

Showing what doesn't work is not that helpful

Include the expected results

Why do you want to use a formula, why not just put in the calculated result?

[Paul_Hossler]

Meantime, just guessing at a format and data, you could try something like this

It's not as elegant as it could be




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cFactor As Double

cFactor = Range("C1").Value

Application.EnableEvents = False ' <<<<<<<<<<<<<<<<<<<< Important

With Target.Cells(1, 1)

Select Case .Column
Case 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25
If Len(.Value) = 0 Then
.Offset(0, 1).ClearContents
Else
.Offset(0, 1).Value = .Value / cFactor * 100#
End If

Case 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26
If Len(.Value) = 0 Then
.Offset(0, -1).ClearContents
Else
.Offset(0, -1).Value = .Value * cFactor / 100#
End If
Case Else

End Select
End With


Application.EnableEvents = True


End Sub

[RAECH]

28577I don't have the workbook yet. I am currently writing it and trying to include each aspect as I am going forward so I am sure that my ideas works, before going full scale :-)

However, I have attached the sheet I have for now.

A solution with the calculated result would also work! And might even be better too.

[Paul_Hossler]

It's easy (I think) to understand the logic for the 1.000 USD columns, but it's REALLY not clear what you want to do if numbers are entered into each of the Cent/KG columns cells, or is it just the first or what?

28578

For example, Enter 450,000 and use the 125 to replace the 4,000 with 3,600 or what?

[SamT]

You're trying to overload the Event Sub. IMO, Events subs should only determine which other Procedures to run. This style code structure allows one Event Sub to handle many different event locations.

Pseudocode. edit to suit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit sub 'Something changed several ranges at once
If not Intersect(... rngKGTonnes)... Then Sub 1
If not Intersect(... rngCentKG).... Then sub 2
End Sub


Private sub 1
Application.EnableEvents = False 'Don't trigger another event
'Code to fill in Cents/Kg
Application.EnableEvents = True


Private sub 2
Application.EnableEvents = False
'Code to fill in KG/Tonnes
Application.EnableEvents = True

[SamT]

I got tired of writing all those
With Application
.blah blah
End With
So I use this instead

Private Function SpeedyCode(GoFast As Boolean)
'Usage:
'SpeedyCode True|False

Static Calc As Long
If Not CBool(Calc) then Calc = xlCalculationAutomatic

With Application
.ScreenUpdating = Not GoFast
.EnableEvents = Not GoFast
If GoFast Then
Calc = .Calculation
.Calculation = xlCalculationManual
Else
.Calculation = Calc
.Calculate
Calc = xlCalculationAutomatic
End If
End With

End Function
I also find this very handy

Private Sub ResetApplication
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End Sub

[RAECH]

"but it's REALLY not clear what you want to do if numbers are entered into each of the Cent/KG columns cells, or is it just the first or what?

I apologise if my explanation wasn't that clear. But you were correct in you "guess workbook".

Enter figures in $-column than make a calculation in Cent/Kg with the amount from Cell P19 (in my example wb). And then, if we should enter a number in Cent/KG we should do the opposite, and then calculate back to the $-column.

Because we have different targets in the company. Some requires e.g. a target of 6 Cent/KG, and then to do the forecast/budget we calculated back using the expected production amounts.

I hope this clarified it up.


And thank you for the code! It works how I would like it two.. I might tweak it a bit later to better suit the final workbook.

Best regards

[Paul_Hossler]

Ok, but I'l still not comfortable with the math

1. Enter P17
2. Enter P18
3. If P18 <> 0 then P19 = P17 / P18

4. Enter Q17
5. Then what???

If P19 > 0 then P17 = Q17 / P19 * 100

???

28582

Part of my problem is that I think the UOM are messed up

F17 looks like UOM = #Tons (manually entered)
F18 looks like UOM = $/Ton (manually entered)
F19 looks like UOM = Total cost in $1000 = #Tons * $/Ton /1000 to get cost in K$ (calculated)

It's the col Q cells where I get confused

[RAECH]

I am not doing any calculations in P17:P19. This is general weights for all production = We are not changing this. But using this as a key figure calculation of Cent/KG on our accounts.

Therefor, it is in the section "Cost of Goods Sold" in the example WB I need to do the calculations.
So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
enter in Q24 = calculation in P24 (Formula: Q24*P19/100)

This will then apply to approx 250 accounts - that all use the total production volume for the specific month in P19 (for April in this example)

[Paul_Hossler]

I am not doing any calculations in P17:P19.

Lookinq at Example WB in post 4, it does look like P19 is being calculated based on P17 and P18

So I was expecting that is P17 or P18 were changed, then P19 would be updated

28587





So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
enter in Q24 = calculation in P24 (Formula: Q24*P19/100)

1. This seems like a circular calculation

2. Where did the 923,490 in P24 come from? It's 10 times the P19

28588

[RAECH]

P17 is the expected quantity of animals that we slaughter, and P18 is their average weight. This is not changed when when we enter our forecast numbers into the section of "Cost of Goods Sold". But yes, P19 has a calculation, that we use to calculate our key figure Cent/KG. We are a production company and our focus is on Cent/KG when we make our accounts and look at our EBIT. Because it tells us how well we are doing compared to each KG we produce and sell. Therefore, P19 should not change and we are NOT determining P19 alone, but is found from the expected slaughter quantities and average weights. These KG and weight can be different for each month.

Therefore, we do calculation in P/Q24 that is our accounts. And probably why you find it as a circular calculation but I assure you that it is not :-) Our Purchase of livestock figures in April is 10 times bigger the number in P19 because our forecast is 10USD per KG of meat. For May it is 10.1USD per KG. And that also equals a Cent/KG of 1000.

Therefore, if we should change the price of meat to say 10.2 for April I would correct P24 to 941,959.8 (92349*10.2) - and in that case I need Q24 to automatically change to 1020 Cent/KG.

The reason why I need the reverse, is that for some accounts we use a target of Cent/KG in the forecast - so in these cases I would change Cent/KG and have the workbook calculate back with P19 to the $-value.

[SamT]

So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
enter in Q24 = calculation in P24 (Formula: Q24*P19/100)
No!

If you manually change one, you can calculate the other. But, you can't calculate both

[RAECH]

No!

If you manually change one, you can calculate the other. But, you can't calculate both

Normally I would agree. But in this case, for our purpose I must disagree.
As I will not input the formula into the cells but only the calculated values, so I don't have any circular calculation issues.

If you still think it is a totally NO GO - please advise how you would suggest a solution where I can change the cells as per my need explained in the previous reply from today.

[Paul_Hossler]

Try this to see if it's closer

The only cells it handles are the green ones in each of the monthly columns

28595


Option Explicit

Const rowQty As Long = 17
Const rowAvg As Long = 18
Const rowAmt As Long = 19
Const rowPurch As Long = 24




Private Sub Worksheet_Change(ByVal Target As Range)
Dim colTarget As Long, rowTarget As Long
Dim rQty As Range, rAvg As Range, rAmt As Range, rPurch As Range, rCENT_KG As Range


'remember the location of the first cell changed
With Target.Cells(1, 1)
colTarget = .Column
rowTarget = .Row
End With

'set some range variables to make it easier to keep straight
Select Case colTarget
Case 16, 24, 32, 40, 48, 56, 64, 72, 80
Set rQty = Cells(rowQty, colTarget)
Set rAvg = Cells(rowAvg, colTarget)
Set rAmt = Cells(rowAmt, colTarget)
Set rPurch = Cells(rowPurch, colTarget)
Set rCENT_KG = Cells(rowPurch, colTarget + 1)

Case 17, 25, 33, 41, 49, 57, 65, 73, 81
Set rQty = Cells(rowQty, colTarget - 1)
Set rAvg = Cells(rowAvg, colTarget - 1)
Set rAmt = Cells(rowAmt, colTarget - 1)
Set rPurch = Cells(rowPurch, colTarget - 1)
Set rCENT_KG = Cells(rowPurch, colTarget)
Case Else
Exit Sub
End Select

Application.EnableEvents = False

'calc amount
If Len(rQty.Value) = 0 Then
rAmt.ClearContents
ElseIf Len(rAvg.Value) > 0 Then
rAmt.Value = rQty.Value / rAvg.Value
End If


'do caclucation that I don't understand
Select Case colTarget
Case 16, 24, 32, 40, 48, 56, 64, 72, 80
If rAmt.Value > 0 Then
rCENT_KG.Value = rPurch.Value / rAmt.Value * 100
End If

Case 17, 25, 33, 41, 49, 57, 65, 73, 81
rPurch.Value = rCENT_KG * rAmt.Value / 100#
End Select


Application.EnableEvents = True




End Sub

[SamT]

Normally I would agree.
Disagree all you want. that won't change the facts.

As I understand your issue, you enter a new value into one of two Cells on a Row. Then you need the other Cell in that Row to have a value that is calculated from the value entered into the first Cell.

Is that correct?

Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?

I can tell by Paul's use of Target(1,1) that he expects some critical Cells to be changed via other Procedures that change many Cells below and to the right of the crucial Cells at the same time. Since there is no simple method of properly handling all situations like that, I ignore them with Target.Count > 1, and depend on the other programmer to avoid Side Effects.

The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.

[Paul_Hossler]

Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?

The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.

1. The way I understand it, there is a pair of columns for each month. Col P-Q is the pair for April 2021 with 9 months left in 2021

2. While I agree in general about putting only event code in an event handler, each situation is different.

I certainly wouldn't include code to (for ex) calculate and print a 5 year acounts summary in the event handler, in THIS case since there are only 3 calculations -- all of which use cells on the sheet to put values on the sheet -- I made a judgement that I wasn't overloading the event handler.

A standard module sub could have been used, but I felt that the increase in complexity wasn't worth it

Bottom line = matter of personal style and a judgement call

[SamT]

Bottom line = matter of personal style and a judgement call
Absolutely.


The way I understand it, there is a pair of columns for each month. Col P-Q is the pair for April 2021 with 9 months left in 2021
That makes more sense. I have yet to look at the OP's attachment, so I have been missing a lot.

Going with the premise that a User will not be changing Columns out of date and for more robustness

If Not Intersect(Target, ThisMonthsColumns) is Nothing Then...


Private Function ThisMonthsColumns() As Variant
Set Found = Find Todays month
Set ThisMonthsColumns to Range(Found.Offset(, -2)Resize(1, 4).EntireColumn.Address) 'Include previous month


Which (different) Procedure to run should be determinable by

If CBool(Target.Column Mod 2) Then... Else...

[RAECH]

As I understand your issue, you enter a new value into one of two Cells on a Row. Then you need the other Cell in that Row to have a value that is calculated from the value entered into the first Cell.

Is that correct?

Yes correct. I need both values to depend on each other - and it varies from time to time which of the two we know.



Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?
Two columns for each month. And with full year of accounting it is 24 columns




The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.

Ok. Thanks for info - I am still quite new to VBA, and I see your point in keeping the Event sub clean. So for know I have chosen to use your earlier suggestion to call a procedure instead.

Thanks to both of your for your inputs. I managed to make it work with this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub


If Not Intersect(Target, Range("P24:P30")) Is Nothing Then
Target.Offset(0, 0).Activate
Call ForecastPeriod1_DKK
Target.Offset(1, 0).Activate
End If


... Ect
End Sub

And then calling:

Sub ForecastPeriod1_DKK()

Application.EnableEvents = False


Set acc_mth1 = ActiveCell


acc_mth1.Offset(0, 1).Formula = "=" & acc_mth1.Address & "/$P$19*100"
acc_mth1.Offset(0, 1).Copy
acc_mth1.Offset(0, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False


Application.EnableEvents = True


End Sub

However, for the Event sub. I am thinking of setting it up, so it only works in cells with a specific colour code - instead of specifying ranges.
Does that sound like a good idea, or do you have a good proposition?

[SamT]

However, for the Event sub. I am thinking of setting it up, so it only works in cells with a specific colour code - instead of specifying ranges.
Does that sound like a good idea, or do you have a good proposition?
Bad idea. you can only check colors in a Range, so that would be two steps vs one. Colors change: A user might not like the colors you like, Opening a file on a different computer might change the colors. Stick with Ranges and Values.

I finally opened your example in post #4, and now understand Paul's frustrations. You have many Hard numbers with no reference as to their meaning/association.

From a programmers viewpoint, there are several issue with the sheet: There are Actuals, Budgets, Forecasts and Variables on the same sheet. Each Monthly table has empty columns in it. Monthly tables; Actual, Budget, and Forecast should be compacted with empty columns only between tables.

For monthly Table labels use real Dates Formatted as desired, do not use simple Strings like "April" or "May, 2021" .For Forecasting, use 13 Monthly Tables so Jan of next year is present. You can use 14 Tables, Last Dec to Next Jan, but I don't see any advantage.

A very common amateur mistake is trying to conserve Excel Real Estate. Worksheets are cheap, pennies on the thousand. Use a different sheet for each Class of Data (Actuals, Budgets, Forecasts).

For Data Tables (Actual, Budget, and Forecast): Row 1 = Month/Table Names; Row 2 = Field names / Column Headers; Row 3 and below = Data. The only formatting is Table and Field Names should be a larger Font and Bold. the Field Names Row should be Underlined. Numerical Columns should be numerically formatted as desired (Accounting, Dollar/Pound/Euro signs, Number of decimals. etc. Do not use thousands separators. Column "A" should be Row Labels, Font Bold, no borders, and Column B should be empty. Data Sheets usually have no Formulas, only Code.

For Management's pleasure, create Report sheets that pull Data from the necessary Data Sheets. Reports usually have many Formulas, but no code. As you advance in knowledge, create a Dashboard sheet. These Reports and Dashboards can be automatically emailed to Directors as desired. Reports sheets should be formatted to be informative and very eye pleasing.

The difference between Data and Reports is Data is for Programmers and Reports are for Users.

The Code to convert Dollars/lb to Cents/Kg can be incorporated into the Actuals Sheet to assist in Formulating Reports.

As you learn more, you will be wanting to keep User's hands off the Data sheets by creating VBA UserForms so simple Data entry clerks (and others) can input data safely and correctly. The data layout structure above enables this.

Code Follows Data: So you need to make the Data sheets first, the above Data Structures make the Code algorithms obvious . When you're uncertain of formulas etc.leave the cell empty but with a Comment/Note.

Upload/attach it when done with the Data Sheets.