VBA IF Else Conditions-CodePudding

I'm new to VBA and I am having trouble with my embedded if statements. What I'm trying to do is have my loop, loop through my arrays for CC numbers and the group names for those CC numbers. What my code is supposed to do is filter through the main source file, filter for the CC numbers, create new worksheets for those CC numbers, then create a new sheet with a pivot table for those new group worksheets.

So far my code is successful except there is a situation where a CC number won't appear in the raw file and I want it to delete the new ws and go to the next CC number before going through the below steps of creating a new sheet, pivot tables, etc. Hopefully that makes sense.

My error is at this line:

    If dataRG Is Nothing Then subWS.delete

Would appreciate any help.

'Loop through array for sheet names
For n = UBound(wsNames) To LBound(wsNames) Step -1
    Set subWS = wb.Worksheets.Add(After:=ws)
    'rename ws using sheet names array
    subWS.Name = wsNames(n)
    If IsArray(ccNumbers(n)) Then 'multiple group numbers in array
        dataRG.AutoFilter 7, ccNumbers(n), xlFilterValues
    Else
        dataRG.AutoFilter 7, ccNumbers(n) 'x;And' is default (irrelevant) 
    End If
    If dataRG Is Nothing Then subWS.delete
    End If
    Set dfCell = subWS.Range("A1")
    'copy column widths
    dataRG.Rows(1).Copy
    dfCell.PasteSpecial xlPasteColumnWidths
    'select first cell as selection is first row by product of 'PasteSpecial
    dfCell.Select
    'copy visible cells only
    dataRG.SpecialCells(xlCellTypeVisible).Copy dfCell
    'set range for subws
    Set subRG = subWS.Range("A1").CurrentRegion
    'Format each sheet as a table
    subWS.ListObjects.Add(SourceType:=xlSrcRange, Source:=subRG).Name = TbleNames(n)
    'Add new WS for pivots
    Set pvtWS = Sheets.Add(After:=subWS)
    pvtWS.Name = PvtNames(n)
    'Define Pivot Caches
    Set subCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=subRG)
    'Create Pivot Tables
    Set subPvtTable = subCache.CreatePivotTable(TableDestination:=pvtWS.Cells(1, 1), TableName:="1")
Next n

CodePudding user response：

In your code you are testing whether dataRG is Nothing but by the time you test that it should already be something as you have just executed some code on the dataRG range (In the If...else statement on ccNumbers)

The processing portion of your code appears to be entirely contingent on dataRG not being Nothing. Which I presume is to determine whether or not your Autofilter has returned results.

So why don't you encapsulate the whole setting portion of your code in an If statement based on the returned results?

like below, for example

'Loop through array for sheet names
For n = UBound(wsNames) To LBound(wsNames) Step -1
    
    If IsArray(ccNumbers(n)) Then 'multiple group numbers in array
        dataRG.AutoFilter 7, ccNumbers(n), xlFilterValues
    Else
        dataRG.AutoFilter 7, ccNumbers(n) 'x;And' is default (irrelevant)
    End If
    
    Set rg = ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    
    'If Not dataRG Is Nothing Then
    If rg.Rows.Count > 1 Then 'please note that if your table doesn't have headers you should use 0 i.s.o. 1 here
        
        Set subWS = wb.Worksheets.Add(After:=ws)
        Set subRG = subWS.Range("A1").CurrentRegion
        Set pvtWS = Sheets.Add(After:=subWS)
        Set dfCell = subWS.Range("A1")
        
        rg.Rows(1).Copy
        
        dfCell.PasteSpecial xlPasteColumnWidths
        rg.Copy dfCell
        
        subWS.Name = wsNames(n)
        subWS.ListObjects.Add(SourceType:=xlSrcRange, Source:=subRG).Name = TbleNames(n)
                
        pvtWS.Name = PvtNames(n)
        
        Set subCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=subRG)
        Set subPvtTable = subCache.CreatePivotTable(TableDestination:=pvtWS.Cells(1, 1), TableName:="1")
    End If
Next n