Replace cell values in multiple columns
 

I have a worksheet that has 51 columns, each column is a state label, that
shows a status of "1" or "0". This includes is a column that indicates "ALL
STATES". I am looking to see how to write a macro that would look at "All
States" (column M) and if "1" than look in same row for each state and
replace if remaining states are equal to "1" (1- 50) to a "0"
Then do this for each row.
Example:
ALL STATES AK AL CA CO CT
1 1 1 0 1 0

Final Results
ALL STATES AK AL CA CO CT
1 0 0 0 0 0

Not sure how to write this in code.

Replace cell values in multiple columns
 

Assuming you have "All States" in column A, right click your tab and view
code and then paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

If Not Intersect(Target, Columns("A:A")) Is Nothing And Target.Value = 1 Then
For i = 1 To 50
Target.Offset(0, i).Value = 0
Next i
End If

End Sub

"Jen_T" wrote:

I have a worksheet that has 51 columns, each column is a state label, that
shows a status of "1" or "0". This includes is a column that indicates "ALL
STATES". I am looking to see how to write a macro that would look at "All
States" (column M) and if "1" than look in same row for each state and
replace if remaining states are equal to "1" (1- 50) to a "0"
Then do this for each row.
Example:
ALL STATES AK AL CA CO CT
1 1 1 0 1 0

Final Results
ALL STATES AK AL CA CO CT
1 0 0 0 0 0

Not sure how to write this in code.

Replace cell values in multiple columns
 

Sorry, I mean paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo errorcatcher

Dim i As Integer

If Not Intersect(Target, Columns("A:A")) Is Nothing And Target.Value = 1 Then
For i = 1 To 50
Target.Offset(0, i).Value = 0
Next i
End If

errorcatcher:
Application.EnableEvents = True

End Sub

Much safer.

"Jen_T" wrote:

I have a worksheet that has 51 columns, each column is a state label, that
shows a status of "1" or "0". This includes is a column that indicates "ALL
STATES". I am looking to see how to write a macro that would look at "All
States" (column M) and if "1" than look in same row for each state and
replace if remaining states are equal to "1" (1- 50) to a "0"
Then do this for each row.
Example:
ALL STATES AK AL CA CO CT
1 1 1 0 1 0

Final Results
ALL STATES AK AL CA CO CT
1 0 0 0 0 0

Not sure how to write this in code.

Replace cell values in multiple columns
 

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Change_State()
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "M").End(xlUp).Row
Set MyRange = Range("M2:M" & lastrow)
For Each c In MyRange
If c.Value = 1 Then
c.Offset(, 1).Resize(, 50).Value = 0
End If
Next
End Sub

Mike

"Jen_T" wrote:

I have a worksheet that has 51 columns, each column is a state label, that
shows a status of "1" or "0". This includes is a column that indicates "ALL
STATES". I am looking to see how to write a macro that would look at "All
States" (column M) and if "1" than look in same row for each state and
replace if remaining states are equal to "1" (1- 50) to a "0"
Then do this for each row.
Example:
ALL STATES AK AL CA CO CT
1 1 1 0 1 0

Final Results
ALL STATES AK AL CA CO CT
1 0 0 0 0 0

Not sure how to write this in code.