VBA: How can I skip rows containing not containing “A” while in a nested loop?

Solution for VBA: How can I skip rows containing not containing “A” while in a nested loop?
is Given Below:

I am relatively new to VBA.

I have 2 files: (1) Master file (2) Monthly Stats file. I am trying to loop through a column in the Master File to see if it corresponds with the Monthly file. If it matches, then extract the name from Master file. However, there are a lot of N/A inputs in the column, and I only want to loop through rows that start with the string “A”. As I already have a nested loop, I cannot type another Next A. Here is my code:

Sub SummaryData()

Dim CustCode As String
Dim CustName As String
Dim wkb As Workbook
Dim Count As Integer

Workbooks.Open Filename:=Sh_Source.Range("HKS").Value

Set wkb = Workbooks("DataHK.xlsx")

For A = 2 To Sh_Main.Range("E" & Rows.Count).End(xlUp).Row
        
    If Left(Range("E" & A).Value, 1) = "A" Then
    CustCode = Sh_Main.Range("E" & A).Value
    
    Else
    'Not sure what to put'
    
    Count = 0
    CustName = ""
        
        For b = 2 To wkb.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
                
            X = wkb.Sheets("Data").Range("C" & b).Value
                
            If CustCode = X Then
                CustName = Sh_Main.Range("F" & A).Value
                Count = Count + 1
            
            Else
            End If
            Next b
      
     Sh_Body.Range("A" & A).Value = CustCode
     Sh_Body.Range("B" & A).Value = CustName
     Sh_Body.Range("C" & A).Value = Count
     
Next A

wkb.Close savechanges:=False

End Sub

Any help would be greatly appreciated as I am new to this, thank you!

If all you want to do is skip the rest of the current iteration of the loop, you could use goto. I know people dislike it, but it can be simpler than other solutions. In this case, put skip: right above the Next A line, and then in your else condition put goto skip where you have ‘Not sure what to put’ and then close your if statement, like this:

    If Left(Range("E" & A).Value, 1) = "A" Then
        CustCode = Sh_Main.Range("E" & A).Value
    Else
        Goto skip
    End if

so that the whole code would be

Sub SummaryData()

Dim CustCode As String
Dim CustName As String
Dim wkb As Workbook
Dim Count As Integer

Workbooks.Open Filename:=Sh_Source.Range("HKS").Value

Set wkb = Workbooks("DataHK.xlsx")

For A = 2 To Sh_Main.Range("E" & Rows.Count).End(xlUp).Row
        
    If Left(Range("E" & A).Value, 1) = "A" Then
        CustCode = Sh_Main.Range("E" & A).Value
    Else
        Goto skip
    End If
    
    Count = 0
    CustName = ""
        
        For b = 2 To wkb.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
                
            X = wkb.Sheets("Data").Range("C" & b).Value
                
            If CustCode = X Then
                CustName = Sh_Main.Range("F" & A).Value
                Count = Count + 1
            
            Else
            End If
            Next b
      
     Sh_Body.Range("A" & A).Value = CustCode
     Sh_Body.Range("B" & A).Value = CustName
     Sh_Body.Range("C" & A).Value = Count
     
     skip:
     
Next A

wkb.Close savechanges:=False

End Sub

Generally people avoid goto (for some good reasons and some bad ones), but I do think that it can be useful and I don’t think it’s inherently bad. However, most people in your case would put the entire block inside the if statement, like this:

Sub SummaryData()

Dim CustCode As String
Dim CustName As String
Dim wkb As Workbook
Dim Count As Integer

Workbooks.Open Filename:=Sh_Source.Range("HKS").Value
Set wkb = Workbooks("DataHK.xlsx")

For A = 2 To Sh_Main.Range("E" & Rows.Count).End(xlUp).Row
        
    If Left(Range("E" & A).Value, 1) = "A" Then
    
        CustCode = Sh_Main.Range("E" & A).Value
        Count = 0
        CustName = ""
        
        For b = 2 To wkb.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
                
            X = wkb.Sheets("Data").Range("C" & b).Value
                
            If CustCode = X Then
                CustName = Sh_Main.Range("F" & A).Value
                Count = Count + 1
            End If
            
            Next b
      
     Sh_Body.Range("A" & A).Value = CustCode
     Sh_Body.Range("B" & A).Value = CustName
     Sh_Body.Range("C" & A).Value = Count
     
    End If
     
Next A

wkb.Close savechanges:=False

End Sub

And then the code inside the if block will only execute if the condition is met and there’s no need to try to skip the rest of the for loop.

I will note that some of your variables don’t seem* to refer to anything, particularly the sheet? variables: Sh_Source, Sh_Main, and Sh_Body. Without providing a reference for those, this code will fail.

  • it’s possible they are defined outside of the sub you have provided