Macro to calculate value in a cell once the other cell in the same row becomes non-blank

acmehra

New Member
Joined
Jan 20, 2018
Messages
16
Hi Friends ,

I am trying to calculate value in a cell as soon as other cell of the same row becomes non-blank. For example :

Once the value in cell G1 > 0 then system should auto calculate the value in coressponding cell of H i.e H1.

Similarly once G2> 0 then value of H2 should be calculated & so on.

Here Column G and H are fixed only rows will vary.

While calculating the value of H1 ,

if B2 = "TEXT1" then
H1 = G1 * 0.345

If B2 = "TEXT2" then
H1 = G1 * 0.789

Similarlly value of H2 is to be calculated once G2>0 & so on.

Here Cell B2 is fixed( it have drop down value TEXT1 , TEXT2) , means for all the cells of column H , we will be referring value of cell B2 only.

Please help.

Thanks in advance.


Regards
Achal
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Is this what you want?
copy the formula down as need.


Excel 2010
ABCDEFGH
121.578
2Text2
Sheet1
Cell Formulas
RangeFormula
H1=IF(G1="","",IF($B$2="text1",G1*0.345,G1*0.789))
 
Upvote 0
Is this what you want?
copy the formula down as need.

Excel 2010
ABCDEFGH
Text2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.578[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=IF(G1="","",IF($B$2="text1",G1*0.345,G1*0.789))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes , I want the same thing. Only issue is B2 is having around 10 different options . So instead of writing a nested "if" formula in the column H , I wanted to write a vb code which would be more neat way to manage.
 
Upvote 0
maybe
Right click on the tab you want the code to run in and select view code
Past the code into the VBA editor. The code will update column H when a new selection in B2 is made or a number is put into column G. Note that if the number in column G is from a formula change code does NOT run when the formula updates.


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If (Target.Column = 7 And IsNumeric(Target)) Or Target.Address = Range("B2").Address Then
    Dim lr As Long
    Dim dvrng As String
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    dvrng = Range("B2")
    
    For x = 1 To lr
        Range("H" & x).ClearContents
        Select Case dvrng
            Case "text1"
                If Range("G" & x) <> "" Then Range("H" & x) = Range("G" & x) * 0.345
            Case "text2"
                If Range("G" & x) <> "" Then Range("H" & x) = Range("G" & x) * 0.789
           
                [COLOR=#008000]'contiune the select case for all the posable values in B2[/COLOR]
        End Select
    Next x
End If
Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top