Simple Auto Multiply

[mamun_ges]

Hi There,
I tried recording a macro but does not serve my purpose. Hope someone help.
I have data in E1
the range is G starts from G3 to the last row
I want to multiply G3 and E1 in H3, G4 and E1 in H4 as same to the last row.
If range G3:G or E1 is blank it returns zero. and It updates automatically.

Thanks

 

[mumps]

Place this formula in H3 and copy it down to the last row with data:
=$E$1*G3

 

[mamun_ges]

Thanks for the reply.

Want it in VBA code, Hope some one help

 

[mumps]

Using the formula will update column H automatically. If you want to use VBA, you will have to run the macro manually each time to update column H. It is possible to run a macro automatically based on a change in a cell. For example, the data would be updated automatically when you change the value in E1 or any value in column G. Please advise.

 

[mamun_ges]

Thanks,
I do not want to use formula because there is some condition. I want to run macro automatically based on cell change.
Yes, The value in E1 or any value in column G has to update. and sometimes a new row is added in various points. So it will be helpful
if vba code is always calculated the formula.

Thanks

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change the value in E1 or in column G and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E1,G:G")) Is Nothing Then Exit Sub
    Dim lRow As Long
    lRow = Range("G" & Rows.Count).End(xlUp).Row
    Range("H3:H" & lRow).Formula = "=$E$1*G3"
    Range("H3:H" & lRow).Value = Range("H3:H" & lRow).Value
End Sub

 

[mamun_ges]

Thanks, it works as expected.

 

[mumps]

You are very welcome.