Code debugs when array is not more than 1

[VeeBa]

Hi All - I have below code which loops an array (filters column A) to create a report per business. However, I am encountering an error everytime my arr is just 1 business. Can you help me modify below code which will proceed even though there is just 1 business in column A? Thank you!

For i = 1 To UBound(MyArr)  'loop through array values one at a time
    'Filter column A by the current value
        Range("A:A").AutoFilter Field:=1, Criteria1:=MyArr(i)
        
    'Create a new blank sheet named for the current array value
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(i)
        ws.Activate
    'Copy current filtered rows to new sheet, values only and formatting preserved
        Range("B1:W" & LR).EntireColumn.Copy
        Sheets(MyArr(i)).Range("A1").PasteSpecial xlPasteValues
        Sheets(MyArr(i)).Range("A1").PasteSpecial xlPasteFormats
    'Count how many rows were moved for message later
        MyCount = MyCount + Sheets(MyArr(i)).Range("A" & Rows.Count).End(xlUp).Row - 1
    'Tighten up appearance
        Sheets(MyArr(i)).Columns.AutoFit
    'Move new sheet to workbook of its own
        Sheets(MyArr(i)).Move
        
        Call CreatePivots
        
    'Save new workbook with array value as name, then close
        ActiveWorkbook.SaveAs FolderName2 & "WD 5 Actual Line Items_" & MyArr(i) & ".xlsx"
        ActiveWorkbook.Close False
    'reset the autofilter
        Range("A:A").AutoFilter Field:=1
    'End If
    

PctDone = i / UBound(MyArr)
UpdateProgressBar PctDone
Next i      'Loop to next array value

 

[StephenCrump]

You just need MyArr to still be an array (i.e. 1 x 1) instead of a scalar, if it contains only one vaue.

How are you populating MyArr?

 

[VeeBa]

You just need MyArr to still be an array (i.e. 1 x 1) instead of a scalar, if it contains only one vaue.

How are you populating MyArr?

Hello - im using below code to populate myarr

'Create a unique list of values from column A
    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BB1"), Unique:=True
'Sort the list alphabetically
    Columns("BB:BB").Sort Key1:=Range("BB2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Put the list into an array in memory
    MyArr = Application.WorksheetFunction.Transpose(Range("BB2:BB" & Rows.Count).SpecialCells(xlCellTypeConstants))
    
Range("BB:BB").Copy
Worksheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteFormulas
Worksheets("Raw").Activate
Range("BB:BB").Clear        'clear the column of values created so sheet is pristine
Range("A:A").AutoFilter     'Turn on the autofilter

 

[wideboydixon]

If there's only one value then MyArr will just contain that value and won't be an array. You could do something like this perhaps:

'Put the list into an array in memory
    MyArr = Application.WorksheetFunction.Transpose(Range("BB2:BB" & Rows.Count).SpecialCells(xlCellTypeConstants))
    If Not IsArray(MyArr) Then
        TempValue = MyArr
        ReDim MyArr(1 To 1)
        MyArr(1) = TempValue
    End If

WBD

 

[VeeBa]

Wideboydixon, thanks for the reply! I tested and it works! Thanks a lot!