VBA to change table values

hezcal11 · Nov 19th 2016

Hello, I am currently writing a table to enter values. A1:C11. The table has entries of a numerical value between 1 and 3. I would like a macro that, if the value n is entered in Cell A11, then all the other cells on that row change to 1 automatically. IE A1 through to A10. This would also operate for each independent row.

Thanks in advance.

**Kenneth Hobson** · Nov 20th 2016

Re: VBA to change table values

Welcome to the forum!

Change the value of v to suit. Right click the sheet's tab, View Code, and paste.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim col As Integer, v As Variant
  If Target.Column <> 1 Or Target.Cells.Count <> 1 Then Exit Sub
  
  v = 11
  
  On Error GoTo EndSub
  Application.EnableEvents = False
  
  col = Cells(1, Columns.Count).End(xlToLeft).Column
  
  With Target
    If .Value = v Then Range(Cells(.Row, "B"), _
      Cells(.Row, col)).Value = 1
  End With
  
EndSub:
  Application.EnableEvents = True
End Sub

Display More

**royUK** · Nov 20th 2016

Re: VBA to change table values

I would think that you can do this with a Formula. Attach an example workbook

hezcal11 · Nov 21st 2016

Re: VBA to change table values

Thanks for that. Did exactly what I needed.

Thanks

VBA to change table values

Participate now!

Share