CODEWITHSUNDEEP
excelvba
Jennifer
Jennifer

Reputation: 11

How do I automatically put a formula into a cell when a value is changed in another cell?

Basically, if any cell in column N is changed, I want to automatically populate the cell in column J using a formula to determine the correct input, which has nothing to do with the value in column N, just that one has been entered. I don't want to use if statements in all the cells in column J because I also want the user to be able to enter values when the formula doesn't produce one.

This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True

    If Not Intersect(Target, Sheet4.Range("N:N")) Is Nothing Then
     If Target > 0 Then
        Application.EnableEvents = False
        Target.Offset(0, -4).Value = "=IF(I2=32,'176',IF(I2=42,'175',IF(I2=630, '190' ,'""')))"
      ' Reset EnableEvents
        Application.EnableEvents = True
    End If
    End If

End Sub

Upvotes: 0

Views: 271

Answers (3)

BZngr
BZngr

Reputation: 681

If your intent is to use the formula in column J only if it will produce a result, then perhaps:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.HasFormula Then Exit Sub
    
On Error GoTo ResetEvents
    Application.EnableEvents = False
    
    Target = UCase(Target.Cells(1))
    
    If Intersect(Target, Sheet4.Range("N:N")) Is Nothing Then GoTo ResetEvents
    
    If Target > 0 Then
        Target.Offset(0, -4).Value = BuildColumnJContent(Target)
    End If
    
ResetEvents:
    Application.EnableEvents = True
End Sub

Private Function BuildColumnJContent(ByVal Target As Range) As String
    Dim cellID As String
    cellID = "I" & Target.Row
    Select Case Sheet4.Range(cellID)
        Case 32, 42, 630
            BuildColumnJContent = "=IF(" & cellID & "=32,176,IF(" & cellID & "=42,175,IF(" & cellID & "=630, 190 ,"""")))"
        Case Else
            BuildColumnJContent = ""
    End Select
End Function

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53137

Several issues here

  1. In a code behind module, use the Me keyword to refer to the sheet in question
  2. I assume you want each formula to refer to cell on the row it's in
  3. User may update several cells at once via copy/paste, which can include cells in Column N and or Other columns
  4. I assume you are onlt intereseted in when the user inserts a value (not a formula) - this can be modified if required

Your code, refactored

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    On Error Resume Next
        Set rng = Intersect(Target, Me.Range("N:N")).SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        rng.Offset(0, -4).FormulaR1C1 = "=IF(RC[-1]=32,176,IF(RC[-1]=42,175,IF(RC[-1]=630,190,"""")))"
        ' Reset EnableEvents
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 1

TechnoDabbler
TechnoDabbler

Reputation: 1265

Maybe something like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim TargetCell As Range
    
    Application.EnableEvents = False
    For Each TargetCell In Target
        If Not Intersect(TargetCell, Sheet4.Range("N:N")) Is Nothing Then
            If TargetCell.Offset(0, -4).Value = "" Then
                TargetCell.Offset(0, -4).Value = "=IF(I2=32,176,IF(I2=42,175,IF(I2=630, 190 ,"""")))"
            End If
        End If
    Next
    Application.EnableEvents = True

End Sub

Upvotes: 0

Related Questions