PDA

View Full Version : VBA insert formula based on another cell selection



Barryj
09-21-2016, 06:52 PM
I currently have the attached macro inserting a formula when cells in column C are changed, this all works as I want, but what I want to do is only have the formula insert on the active row currently it is refreshing all rows meeting the criteria, how can I modify the macro to only insert in the current row with refreshing all other rows.

The reason I want to do this is that the values that the formulas reference may change but the data that is already there i don't want altered.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Or Target.Column = 15 Or Target.Column = 21 Then
With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
.Formula = "=IF(ISERROR(IF(C2=""A"",B2*$K$2*24,IF(C2=""B"",B2*$K$3*24,IF(C2=""C"",B2*$K$4*24)))),"""",IF(C2=""A"",B2*$K$2*24,IF(C2=""B"",B2*$K$3*24,IF(C2=""C"",B2*$K$4*24))))"

End With
With Range("Q2:Q" & Cells(Rows.Count, "O").End(xlUp).Row)
.Formula = "=IF(ISERROR(IF(O2=""A"",N2*$K$2*24,IF(O2=""B"",N2*$K$3*24,IF(O2=""C"",N2*$K$4*24)))),"""",IF(O2=""A"",N2*$K$2*24,IF(O2=""B"",N2*$K$3*24,IF(O2=""C"",N2*$K$4*24))))"
End With
End If
End Sub


Any assistance would be greatly appreciated

SamT
09-21-2016, 07:59 PM
Every time a cell in a workbook is changed, Excel recalculates all existing formulas.

Your Sub Procedure will need to calculate the values, then put the (static) values into the cell.