Automatically multiply abn in input in a cell with another set value

[Feli]

Hi!

I am in the process of creating a model for calculation CO2 emission by a company, on a yearly basis. So what i am trying to do is automatically mulitply the inputed value in a cell with a set emissionfactor, and then in the same cell get the output with the new "scaled value" value. I would like this multiplication to be set for the whole row as this mulitplication should be able to be used for future yearly inputs...I have tried to show what i mean in the table below, where the red coloured text would be numbers that will vary year to year, but the emission factor would be the constant.... So my goal is to let the user only have to but in the collected data (like 500 000 KWh of windpower electricity) and the output in 2018 should be 500 000 * 0,005? Makes sence?

Thanks in advance for help!

Source	unit	EM (emission factor)	2018	2019	2020	2021
Windpower electricity	KWh	0,005	EM(windpower)* used electricity KWh 2018	EM(windpower)* used electricityKWh 2019	EM(windpower)* used electricity KWh 2020	EM(windpower)* used electricity KWh 2021
Construction	m^2	4	EM(construction)* m^2 construction 2018	EM(construction)* m^2 construction 2019	EM(construction)* m^2 construction 2020	EM(construction)* m^2 construction 2021
Petrol	liter	0,028	EM(petrol)* litter used petrol 2018	EM(petrol)* liter used petrol 2019	EM(petrol)* liter used petrol 2020	EM(petrol)* liter used petrol 2021

 

[sijpie]

Assuming that the 2018 windpower is cell D2.

• The cell D2 is empty (or contains a prompt such as: "Enter EM(Windpower)")
• The user enters 500000 into D2
• Excel changes this to 500000 * C2

Is that correct?

 

[Feli]

Yes exactly!

 

[sijpie]

Here is the code. To make it work you need to place the code in the VBA module for the sheet.
Right click on the tab of the sheet and select "View Code"

Then copy paste the following code into the module that opens.
Then read through the comments to understand what is happening. YOU HAVE TO CHECK the line where the comments start with <<<<<<<<<

Now save the file and test it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'multiply emission factor with entered number
    Dim vEF As Variant   'to store emission factor
   
    If Target.Cells.Count = 1 Then  ' Don't react to changes made by paste in several cells
   
        With Range("A1")    '<<<<<<<<<< assuming this is top left cell of table. Else adjust <<<<<<<<<<<<<<<<<<<
            If Not Intersect(Target, .Offset(1, 3).Resize(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count - 3)) Is Nothing Then
                ' the changed cell (target) is within the years columns
                vEF = .Offset(Target.Row - 1, 2)        'store the emission factor for the row
                If IsNumeric(vEF) And vEF <> 0 Then     'emission factor is a number and not 0
                    Application.EnableEvents = False    'stop excel reacting to changes (events), else will get into endless loop
                    Target = Target * vEF
                    Application.EnableEvents = True     'enable the events again
                End If
            End If
        End With
    End If
End Sub

The code module for the sheet is a special module where events happening on the sheet can be intercepted and dealt with. For instance if a user clicks on a cell, or enters a value. In the above code we use this last even: Worksheet_Change().
Excel passes the changed cell(s) to the event sub. It is called 'Target'.
Now we can do things with this cell. First we check if it is within the area where you want to modify the behaviour. If so then we multiply it with the emission factor on the same row.
But if we where to modify it, Excel would call this sub again with the new value. It would start an endless loop and Excel would hang. So we tell VBA to momentarily switch off the event handler while we enter the new value into the cell.