Frimousse
10-25-2009, 05:14 AM
Hi,
I am trying to fill an array with a for..each cosntruct. I am just hoping this will speed up the process as a for..next combined with a paste in range is rather slow.
here is the code I have.
Sub test_sector_array()
Dim totsect(1 To 24, 1 To 2, 1 To 50) As Variant
Dim i, j As Integer
Dim MyArr As Variant
MyArr = Array("Automobiles & Components", "Banks", "Capital Goods", "Commercial & Professional Serv", "Consumer Durables & Apparel", "Consumer Services", "Diversified Financials", "Energy", "Food & Staples Retailing", "Food Beverage & Tobacco", "Health Care Equipment & Servic", "Household & Personal Products", "Insurance", "Materials", "Media", "Pharmaceuticals, Biotechnology", "Real Estate", "Retailing", "Semiconductors & Semiconductor", "Software & Services", "Technology Hardware & Equipmen", "Telecommunication Services", "Transportation", "Utilities")
For i = 1 To 24
j = 1
For Each cell In Range("E20", Range("E20").End(xlDown))
If cell.Value = MyArr(i) Then
totsect(i, 1, j) = cell.Offset(0, -3)
totsect(i, 2, j) = cell.Offset(0, 4)
j = j + 1
End If
Next cell
Next i
End Sub
I am getting the error at the red line. When I use the debug.print MyArr(i) I do not get any problem. I suppose the line tries to compare two differnt type of data and this is a simple syntax issue. Any help appreciated.
Cheers!
I am trying to fill an array with a for..each cosntruct. I am just hoping this will speed up the process as a for..next combined with a paste in range is rather slow.
here is the code I have.
Sub test_sector_array()
Dim totsect(1 To 24, 1 To 2, 1 To 50) As Variant
Dim i, j As Integer
Dim MyArr As Variant
MyArr = Array("Automobiles & Components", "Banks", "Capital Goods", "Commercial & Professional Serv", "Consumer Durables & Apparel", "Consumer Services", "Diversified Financials", "Energy", "Food & Staples Retailing", "Food Beverage & Tobacco", "Health Care Equipment & Servic", "Household & Personal Products", "Insurance", "Materials", "Media", "Pharmaceuticals, Biotechnology", "Real Estate", "Retailing", "Semiconductors & Semiconductor", "Software & Services", "Technology Hardware & Equipmen", "Telecommunication Services", "Transportation", "Utilities")
For i = 1 To 24
j = 1
For Each cell In Range("E20", Range("E20").End(xlDown))
If cell.Value = MyArr(i) Then
totsect(i, 1, j) = cell.Offset(0, -3)
totsect(i, 2, j) = cell.Offset(0, 4)
j = j + 1
End If
Next cell
Next i
End Sub
I am getting the error at the red line. When I use the debug.print MyArr(i) I do not get any problem. I suppose the line tries to compare two differnt type of data and this is a simple syntax issue. Any help appreciated.
Cheers!