CODEWITHSUNDEEP
excelvbaloopsfor-loop
Ben Gerry
Ben Gerry

Reputation: 15

VBA - Nested For loops not working

I'm putting together a quick script to compile some data from a table into a more "uploadable" format. I've got a triple For loop to cycle through 6 worksheets, looking at "cluster" names in the range A2:A100 and picking the branch numbers in each "cluster" in the range defined below as H1, J1, L1, etc.

The code compiles but doesn't seem to skip the blank cells. If a blank branch number is found I'd like the script to move on to the next cluster, and if a blank cluster is found for it to move on to the next sheet until it's finished looking at all 6.

The data I'd like output is by column: Cluster Name | Rebate | Branch Number, and my script is currently returning all three fields when a branch number is found to be blank, when it should be skipped! I hope this makes sense. My code is below - would you please take a look and let me know what's wrong?

Sub Clusterise()

Dim WSCount, Sheet As Integer
Dim Counter, CCounter As Integer

Dim Clusters, C As Range
Dim Branches, B As Range

Counter = 0

WSCount = ActiveWorkbook.Worksheets.Count

For Sheet = 2 To WSCount

    Set Clusters = Worksheets(Sheet).Range("A2:A100")
    Set Branches = Worksheets(Sheet).Range("H1, J1, L1, ...etc. up to BN1") 'Too long to display it all

    CCounter = 1

    For Each C In Clusters

        If Not IsEmpty(C) Then

        For Each B In Branches

            If Not IsEmpty(B.Offset(CCounter, 0)) Then

            Sheets("Clusterisor").Range("A1").Offset(Counter, 0) = C.Value
            Sheets("Clusterisor").Range("B1").Offset(Counter, 0) = C.Offset(0, 5).Value
            Sheets("Clusterisor").Range("C1").Offset(Counter, 0) = B.Offset(CCounter, 0).Value

            Counter = Counter + 1

            End If

            Next B

        CCounter = CCounter + 1

        End If

    Next C

Next Sheet

End Sub

Upvotes: 1

Views: 547

Answers (1)

Vityata
Vityata

Reputation: 43595

TLDR:

Change If Not IsEmpty(C) Then to If C <> "" Then

IsEmpty() MSDN:

Returns a Boolean value indicating whether avariable has been initialized. Syntax - IsEmpty(expression) The required expression argument is a Variant containing anumeric orstring expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name. Remarks: IsEmpty returns True if the variable is uninitialized, or is explicitly set toEmpty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

What is the different between IsNull, IsEmpty, =Empty, and an empty string ie "" and why might I use variants

IsEmpty() should be used only to check, whether a variable is initialized. What does this mean? In VBA, once you declare a variable without the word New, it is not initialized. Check this:

Public Sub TestME()

    Dim a As Range            'Declaring the variable
    Debug.Print IsEmpty(a)    'True

    Set a = Range("A1")       'Initializing the variable
    Debug.Print IsEmpty(a)    'False

End Sub

Upvotes: 2

Related Questions