View Full Version : [SOLVED:] I need a shortcut (because my method requires too much typing!)
K. Georgiadis
03-30-2005, 12:59 PM
What is the shortcut for setting
value = 1
for cells B17, B26, B40, B48, B68 simultaneously in worksheets "Alpha," "Beta", Gamma" and "Theta"?
Jacob Hilderbrand
03-30-2005, 01:02 PM
Try this macro.
Sheets(Array("Alpha", "Beta", "Gamma", "Theta")).Select
Range("B17, B26, B40, B48, B68").Value = 1
K. Georgiadis
03-30-2005, 01:24 PM
Nope, it only changed the values in the first sheet in the array
Zack Barresse
03-30-2005, 01:29 PM
No testing, but maybe ...
Option Explicit
Sub ChangeValsPlease()
Dim wsArr() As String, i As Long
wsArr = Array("Alpha", "Beta", "Gamma", "Theta")
For i = LBound(wsArr) To UBound(wsArr) Step 1
Sheets(wsArr(i)).Range("B17, B26, B40, B48, B68").Value = 1
Next
End Sub
Jacob Hilderbrand
03-30-2005, 01:31 PM
Try this.
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
Case Is = "Alpha", "Beta", "Gamma", "Theta"
ws.Range("B17, B26, B40, B48, B68").Value = 1
End Select
Next ws
K. Georgiadis
03-30-2005, 01:56 PM
I get a runtime error on Zack's, but DRJ's code works. Thanks!:beerchug:
Zack Barresse
03-30-2005, 02:49 PM
Oh, right. Don't Dim as a String then. ..
Dim wsArr(), i As Long
But glad you got it working. :yes
Jacob Hilderbrand
03-30-2005, 03:14 PM
Glad to help :beerchug:
Take Care
K. Georgiadis
03-30-2005, 07:03 PM
Zack, I tried your amended code and I can confirm that it works. Thanks!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.