Set value in a table cell based on selection from list in another cell on the same row.

[vanwooten]

I have a table where I use a macro to add a new row. A cell on that row selects its value from a list. I would like to be able to set the value of another cell on the same row with an If, then else. I've found pieces like the Worksheet_Change event but my code crashes excel:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rs As Worksheet
Set rs = ThisWorkbook.Worksheets("Resources")

If ActiveCell.Value = "DTS" Then
ActiveCell.Offset(0, 21).Value = rs.Range("J82")
End If


End Sub

 

[DanteAmor]

Try this:
Change "B:B" to the column where it captures the "DTS" value.
Change "J" to the column you want to select.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "DTS" Then
      Range("J" & Target.Row).Select
    End If
  End If
End Sub

 

[vanwooten]

So the source of the value that I want to set is the rs.Range("J82"). So does that .select statement become:

Range ("AB" & Target.Row).value = rs.Range("J82")

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    
    Dim sh As Worksheet
    Set sh = Sheets("Resources")
    
    If Target.Value = "DTS" Then
      Application.EnableEvents = False
      Range("AB" & Target.Row).Value = sh.Range("J82")
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[vanwooten]

Thank You for your help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[vanwooten]

I spoke too soon on this Dante. The code below works perfectly a limited number of times in any given session. Restarting the workbook resets it. If make the selection on the first row - perfect. If I change that selection- perfect. Change it again - nothing. No error just nothing (exit sub). Same if I make a selection once on two rows - perfect. Third row or change one of the two - nothing. It may be letting me change all of the existing rows once. I havent found anything to explain it. The routine that adds a table row does not set a value in that column. I'm pretty confident it is the logic around that target.count test. I am obviously looking for it to work each time a selection is made whether on an existing row or one that has been added:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rs As Worksheet
Set rs = ThisWorkbook.Worksheets("Resources")

If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.CountLarge > 1 Then Exit Sub

If Target.Value = "DTS" Then
Application.EnableEvents = False
Range("$AB" & Target.Row).Value = rs.Range("J79")
Else
Range("$AB" & Target.Row).Value = rs.Range("J82")

Application.EnableEvents = True

End If
End If

End Sub

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rs As Worksheet
  Set rs = ThisWorkbook.Worksheets("Resources")
  
  If Not Intersect(Target, Range("G:G")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    
    'Before modifying the cell
    Application.EnableEvents = False
    
    If Target.Value = "DTS" Then
      Range("AB" & Target.Row).Value = rs.Range("J79")
    Else
      Range("AB" & Target.Row).Value = rs.Range("J82")
    End If
    
    'After modifying the cell
    Application.EnableEvents = True
    
  End If
End Sub

Note:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[vanwooten]

You did it!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback