PDA

View Full Version : VBA to loop through an array



simora
01-09-2019, 01:53 AM
I have a column (A) with 12 months January to December in a Column.
I have loaded those months into an array like;



Dim myArray As Variant
myArray = Application.Transpose(Worksheets("Sheet1").Range("A1:A12"))


I have Sheet names with the names of the Array names

How can I loop through that array to act on cells on each sheet in the array.

Something like



For Each sh In myArray
sh.Range("D1").Value = DONE


I get an error " Object Required "

Thanks

mana
01-09-2019, 02:24 AM
For Each sh In Worksheets(myArray)

simora
01-09-2019, 03:12 AM
Thanks mana : (http://www.vbaexpress.com/forum/member.php?61551-mana)

For Each sh In Worksheets(myArray) is correct.That corrected that mistake, however, it will NOT let me select a specific cell on any of he sheets.
i.e. sh.Range("D1").Select

mana
01-09-2019, 03:32 AM
For Each sh In Worksheets(myArray)
sh.select
sh.Range("D1").Select

Paul_Hossler
01-09-2019, 07:08 AM
You could get rid of the A1:A12 list of months if you wanted to

Also, it's usually not necessary to select WS or Cells before acting on them




Option Explicit
Sub test()
Dim i As Long
Dim wsMonth As Worksheet
Dim sMonth As String

For i = 1 To 12
sMonth = Application.GetCustomListContents(4)(i)

Debug.Print sMonth
' Set wsMonth = Worksheets(sMonth)
' wsMonth.Range("D3").Value = 123

Next i
End Sub

'or possibly better

Sub test2()
Dim i As Long
Dim aryMonthSheets(1 To 12) As Worksheet
Dim sMonth As String

For i = 1 To 12
sMonth = Application.GetCustomListContents(4)(i)
Set aryMonthSheets(i) = Worksheets(sMonth)
Next i

aryMonthSheets(3).Range("D3").Value = 123

End Sub