• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
hawkeye_zzz
Asked:
hawkeye_zzz
  • 8
  • 5
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now