VBA .FindNext() Loop Not W

[poseykw16]

Hi all,

I have created a VBA loop that uses multiple .Find combinations to output a single file using a series of ranges defined with a "X" within an excel sheet. The VBA script allows the user to designate any number of fields to be included in the file. When the user selects an odd number of fields the script works as expected, but if the user selects an even number of fields only half of the fields are populated in the file. It populates the first designated record, and then skips the next. It then populates the 3rd, skips the 4th and so on.

Can someone please take a look at the below script and let me know what I am missing?

Open metaFile For Output As #1

'Set Range of cells that should be included in the file for the text Header
Set header = Worksheets("Master_Campaign_Coding_Assigned").Range("NewMetaHeader")

'Writes header values to variable within the selected range
For row = 1 To header.Rows.Count
    For Column = 1 To header.Columns.Count
        cellValue = header.Cells(row, Column).Value
            If Column = header.Columns.Count Then
                Print #1, cellValue
            Else
                Print #1, cellValue,
            End If
    Next Column
Next row

'----------------------------------------------------------------------------------------------------------------------------'

'Create Meta Output Loop
With Worksheets("Parent_Offer").Range("Output_FLG_Meta")
    Set Output_FLG = Worksheets("Parent_Offer").Range("Output_FLG_Meta").Find("X", LookIn:=xlValues)
    If Not Output_FLG Is Nothing Then
        FindAddress = Output_FLG.Address
        Do
'----------------------------------------------------------------------------------------------------------------------------'
        'Set Meta References for Parent_Offer portion
        Set Parent_Offer_Lookup = Output_FLG
            If Parent_Offer_Lookup Is Nothing Then
                MsgBox "Set Output_FLG to X for the offer codes needed in the meta file. Check for errors on the Parent_Offer tab."
                Stop
            Else
                Set Parent_Offer_CD = Parent_Offer_Lookup.Offset(0, -1)
                Set Parent_Offer_NM = Parent_Offer_Lookup.Offset(0, -10)
                Set Parent_Offer_Desc = Parent_Offer_Lookup.Offset(0, -9)
                Set Parent_Offer_CH = Parent_Offer_Lookup.Offset(0, -8)
                Set Offer_Fulfillment_Type = Parent_Offer_Lookup.Offset(0, -7)
                Set Offer_Treatment_Type = Parent_Offer_Lookup.Offset(0, -6)
                Set Offer_Executing_Vendor = Parent_Offer_Lookup.Offset(0, -5)
                Set Offer_Start_DT = Parent_Offer_Lookup.Offset(0, -4)
                Set Offer_End_DT = Parent_Offer_Lookup.Offset(0, -3)
                Set Wave_NM = Parent_Offer_Lookup.Offset(0, -11)
            End If
                
         'Set Meta References for Wave portion
         Set Wave_Lookup = Worksheets("Wave").Range("Wave_NM_VBA").Find(Wave_NM, MatchCase:=True)
             If Wave_Lookup Is Nothing Then
                 MsgBox "Wave_NM '" + Wave_NM + "' was not found. Check for errors on the Wave tab."
                 Stop
             Else
                 Set Wave_CD = Wave_Lookup.Cells(1, 3)
                 Set Wave_NM = Wave_Lookup.Cells(1, 1)
                 Set Wave_Desc = Wave_Lookup.Cells(1, 2)
                 Set CAMPAIGN_NM = Wave_Lookup.Offset(0, -1)
             End If
    
        'Set Meta References for Campaign portion
        Set Campaign_Lookup = Worksheets("Campaign").Range("Campaign_NM_VBA").Find(CAMPAIGN_NM, MatchCase:=True)
            If Campaign_Lookup Is Nothing Then
                MsgBox "Campaign_NM '" + CAMPAIGN_NM + "' was not found. Check for errors on the Campaign tab."
                Stop
            Else
                Set Campaign_CD = Campaign_Lookup.Cells(1, 7)
                Set CAMPAIGN_NM = Campaign_Lookup.Cells(1, 1)
                Set Campaign_Desc = Campaign_Lookup.Cells(1, 2)
                Set Campaign_Type = Campaign_Lookup.Cells(1, 3)
                Set Campaign_Cost = Campaign_Lookup.Cells(1, 4)
                Set Campaign_Start_DT = Campaign_Lookup.Cells(1, 5)
                Set Campaign_End_DT = Campaign_Lookup.Cells(1, 6)
                Set INITIATIVE_NM = Campaign_Lookup.Offset(0, -1)
            End If

        'Set Meta References for Initiative portion
        Set Initiative_Lookup = Worksheets("Initiative").Range("Initiative_NM_VBA").Find(INITIATIVE_NM, MatchCase:=True)
            If Initiative_Lookup Is Nothing Then
                MsgBox "Initiative_NM '" + INITIATIVE_NM + "' was not found. Check for errors on the Initiative tab."
                Stop
            Else
                Set Initiative_CD = Initiative_Lookup.Cells(1, 3)
                Set INITIATIVE_NM = Initiative_Lookup.Cells(1, 1)
                Set Initiative_Desc = Initiative_Lookup.Cells(1, 2)
                Set PROGRAM_NM = Initiative_Lookup.Offset(0, -1)
            End If

        'Set Meta References for Program portion
        Set Program_Lookup = Worksheets("Program").Range("Program_NM_VBA").Find(PROGRAM_NM, MatchCase:=True)
            If Program_Lookup Is Nothing Then
                MsgBox "Program_NM '" + PROGRAM_NM + "' was not found. Check for errors on the Program tab."
                Stop
            Else
                Set BRAND_CD = Program_Lookup.Offset(0, -1)
                Set Program_CD = Program_Lookup.Cells(1, 5)
                Set PROGRAM_NM = Program_Lookup.Cells(1, 1)
                Set Program_Desc = Program_Lookup.Cells(1, 2)
            End If
            
        'Output meta to .txt file. Tab delimited
        codeOutput = BRAND_CD & vbTab & Program_CD & vbTab & PROGRAM_NM & vbTab & Program_Desc & vbTab & Initiative_CD & vbTab & INITIATIVE_NM & vbTab & Initiative_Desc _
             & vbTab & Campaign_CD & vbTab & CAMPAIGN_NM & vbTab & Campaign_Desc & vbTab & Campaign_Type & vbTab & Campaign_Start_DT & vbTab & Campaign_End_DT & vbTab & Campaign_Cost _
             & vbTab & Wave_CD & vbTab & Wave_Desc & vbTab & CELL_CD & vbTab & CELL_NM & vbTab & Cell_Desc & vbTab & SEGMENT_CD & vbTab & SEGMENT_NM & vbTab & Segment_Desc & vbTab & Parent_Offer_CD _
             & vbTab & Parent_Offer_NM & vbTab & Parent_Offer_Desc & vbTab & Parent_Offer_CH & vbTab & Offer_Fulfillment_Type & vbTab & Offer_Treatment_Type & vbTab & Offer_Executing_Vendor _
             & vbTab & Offer_Start_DT & vbTab & Offer_End_DT & vbTab
        Print #1, codeOutput
        
'--------------------------------------------------------------------------------------------------------------------------------------------------
        Set Output_FLG = Worksheets("Parent_Offer").Range("Output_FLG_Meta").Find("X", LookIn:=xlValues, after:=Output_FLG)
        Set Output_FLG = Worksheets("Parent_Offer").Range("Output_FLG_Meta").FindNext(Output_FLG)
        If Output_FLG Is Nothing Then Exit Do
        Loop While Output_FLG.Address <> FindAddress
    End If
End With

Close #1

MsgBox ("File output Successful")
End Sub

 

[poseykw16]

I have isolated the undesired behavior to the below line of code:

Set Output_FLG = Worksheets("Parent_Offer").Range("Output_FLG_Meta").Find("X", LookIn:=xlValues, after:=Output_FLG) 'creating error

I assume the after flag is creating this issue but it cannot be removed without creating an infinite loop. Any help in rectifying this would be greatly appreciated.

 

[Fluff]

Untested but try creating a new range variable (say Temp) & change this

        Set Temp = Worksheets("Parent_Offer").Range("Output_FLG_Meta").Find("X", LookIn:=xlValues, after:=Output_FLG)
        Set Output_FLG = Worksheets("Parent_Offer").Range("Output_FLG_Meta").FindNext(Output_FLG)
        If Output_FLG Is Nothing Then Exit Do
        Loop While Output_FLG.Address <> FindAddress

 

[poseykw16]

That works perfectly! I assume that works because the .FindNext function uses the last .Find and even though it's assigned to a different variable it brings the .Find back to the Output_FLG without looping through it twice?

Thanks! Just trying to understand the overarching logic here as I have a few more loops that will use the same idea to output other files.

 

[Fluff]

I assume that works because the .FindNext function uses the last .Find

That's right. The used to store the found range is irrelevant as far as FindNext is concerned, because you were constantly using the same variable it always skips one value.