Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace all formulas with values in multiple worksheets | Excel Discussion (Misc queries) | |||
Cell for Changing Multiple Values in other columns | Excel Worksheet Functions | |||
Automatically Accept Replace Values in Text-to-Columns macro | Excel Programming | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
total value relating to cell values in multiple columns! | Excel Discussion (Misc queries) |