loop through string array, error '9'

Question

I can't figure our why I get error '9'. I declared the string array, then I would like to use the array values to set the sheet names. What I'm doing wrong?

Dim wsText() As String
Dim sht As Worksheet
Dim wSum As Worksheet
Dim service As String
Dim supplier As String
Dim priceRange As String
Dim price As String
Dim Lrow As Integer, LastRow As Integer

Set sht = ThisWorkbook.Worksheets(4)
Set wSum = ThisWorkbook.Worksheets(Summary)

wsText = Array("<25K", "25K <100K", "100K <250K", "250K <500K", "500K <1M", "1M <5M", "5M <15M", "15M <30M", "30M <50M")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

    For Each element In wsText
        For i = 5 To LastRow
            For j = 13 To 47

                Lrow = wSum.UsedRange.Rows(wSum.UsedRange.Rows.Count).Row + 1

                service = ThisWorkbook.Worksheets(element.Value).Cells(i, 1).Text
                supplier = ThisWorkbook.Worksheets(element.Value).Cells(4, j).Text
                priceRange = ThisWorkbook.Worksheets(element.Value).Cells(2, 1).Text
                price = ThisWorkbook.Worksheets(element.Value).Cells(i, j).Text

                wSum.Cells(Lrow, 1) = service
                wSum.Cells(Lrow, 2) = supplier
                wSum.Cells(Lrow, 3) = priceRange
                wSum.Cells(Lrow, 4) = price
            Next j
        Next i
    Next element


End Sub

score 2 · Answer 1 · answered Aug 16 '16 at 07:59

2

for starters this part is wrong ThisWorkbook.Worksheets(Summary) here Summary is used as variable but cant see assignment to it in your code. That will throw error 9. if the sheet name itself is summary then use it inside double quotes like ThisWorkbook.Worksheets("Summary").

answered Aug 16 '16 at 07:59

cyboashu

10,196
2
27
46

1

++ beat me to it :) Deleted my post – Siddharth Rout Aug 16 '16 at 08:00
1

Thank you very much! I hate my self, that I can't look carefully on the code! – Lóránt Csaba Mihály Aug 16 '16 at 08:04
it happens to all of us:) – cyboashu Aug 16 '16 at 08:09

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

Declare your array as variant instead

e.g.

Dim wsText as variant
wsText = Array("<25K", "25K <100K", "100K <250K", "250K <500K", "500K <1M", "1M <5M", "5M <15M", "15M <30M", "30M <50M")

Reference: Declare and Initialize String Array in VBA

edited May 23 '17 at 11:44

Community

1
1

answered Aug 16 '16 at 08:01

AiRiFiEd

311
2
12

loop through string array, error '9'

2 Answers2