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

cyboashu · Accepted Answer · 2016-08-16 07:59:46Z

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, 2016 at 7:59

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Siddharth Rout Over a year ago

++ beat me to it :) Deleted my post

Lóránt Csaba Mihály Over a year ago

Thank you very much! I hate my self, that I can't look carefully on the code!

cyboashu Over a year ago

it happens to all of us:)

Community · Accepted Answer · 2017-05-23 11:44:23Z

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, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 16, 2016 at 8:01

AiRiFiEd

3112 silver badges12 bronze badges

Collectives™ on Stack Overflow

loop through string array, error '9'

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related