quick macro to switch cells on and off and on again

On sheet: "Control Panel"
I have an array ("array1") of cells C7:C28 with text saying either "On" or "Off"
I have an array ("array2") of cells J7:C28 with numerical values
The two columns match, so J7 relates to C7 etc.

I am looking for a macro which
1. records the state (on/off) of array1
2. for any row where array2=0, turn array1 to off

And a second macro that turns it back to the original state
LVL 1
hawkeye_zzzAsked:
Who is Participating?
 
Arno KosterCommented:
Sub macro_1()
    
    For pos = 1 To 22
        '-- record the values of array1
        states(pos) = Range("C" & pos + 6)
        
        '-- update array1
        If Range("J" & pos + 6) = 0 Then Range("C" & pos + 6) = "Off"
    Next pos

    '-- record state of additional cell
    states(0) = range("C30")
    '-- update additional cell
    range("C30") = 0
    
End Sub

Sub macro_2()

    range("C30") = states(0)
    For pos = 1 To 22
        '-- reset array1 to original values
        Range("C" & pos + 6) = states(pos)
    Next pos

End Sub

Open in new window

0
 
Arno KosterCommented:
place this code in the vba editor  "Control Panel" section
Dim states(22) As String

Sub macro_1()
    
    For pos = 1 To 22
        '-- record the values of array1
        states(pos) = Range("C" & pos + 6)
        
        '-- update array1
        If Range("J" & pos + 6) = 0 Then Range("C" & pos + 6) = "Off"
    Next pos
    
    

End Sub

Sub macro_2()

    For pos = 1 To 22
        '-- reset array1 to original values
        Range("C" & pos + 6) = states(pos)
    Next pos

End Sub

Open in new window

0
 
Arno KosterCommented:
pay attention though that running macro_2 before macro_1 has been executed clears the data from array1 !
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
hawkeye_zzzAuthor Commented:
That's beautiful! Thank you

Only one extra request please:
I want ideally to run this from a button on another sheet. Is that possible?

Could you also kindly explain how the "pos + 6" works? (for curiosity)
0
 
hawkeye_zzzAuthor Commented:
Sorry if it's not too much trouble, I just realised I need to also record state of C30, and set it to zero (always) as part of the same process with array1
0
 
Arno KosterCommented:
sure, if you insert a button on any different sheet in the same workbook, just assign it to the macro

Sheet1.Macro_1

this should be possible as long as you do not make the subroutines private.



0
 
Arno KosterCommented:
The 'pos' variable is used to step through the cells of the array1 and array2 ranges.
because the first cell in array1 is located at the adress "C7", one needs to add 6 to 1 to get the value 7. For the second cell this would be 6+2 => "C8"

0
 
hawkeye_zzzAuthor Commented:
I'm not sure how to do that.
I've put the macros in a general module, and created a button from the forms menu. Right click on the button I just get to choose a macro from the list, no obvious way to rename it.
0
 
Arno KosterCommented:
You first have to make the macro before you can assign a button to it.
When you have placed the macros in a module, you should be able to link it to the button using the "macro_1" or "macro_2" items from the list.
It would be a better idea however to place the macros in the vba section for sheet1, because the code does not relate to a specific sheet.
So you might get into a situation where you are deleting or inserting values on the sheet where the buttons are placed !

If you would like to have the code in a module, update it so that every

Range

Open in new window

is updated to
worksheets("Sheet1").Range

Open in new window


0
 
Arno KosterCommented:
Sorry, that would be

worksheets("Control Panel").Range

Open in new window

0
 
hawkeye_zzzAuthor Commented:
Very quick and very helpful thank you
0
 
hawkeye_zzzAuthor Commented:
Thanks for the extra info as well
0
 
Arno KosterCommented:
you're welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.