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
1							2	1.578
2		Text2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H1 =IF(G1="","",IF($B$2="text1",G1*0.345,G1*0.789)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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