PDA

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