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

[acmehra]

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

 

[Scott T]

Is this what you want?
copy the formula down as need.

Excel 2010
	A	B	C	D	E	F	G	H
1							2	1.578
2		Text2
Sheet1

Cell Formulas
Range	Formula
H1	=IF(G1="","",IF($B$2="text1",G1*0.345,G1*0.789))

 

[acmehra]

Is this what you want?
copy the formula down as need.

Excel 2010

	A	B	C	D	E	F	G	H
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.

 

[Scott T]

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.

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