Need help inserting a loop into another loop

jmcginley3

New Member
Joined
Mar 28, 2018
Messages
14
I have two pieces of working code that I was able to get working based on some things I've found online and some modifications I made myself (I'm new so I'm sure this code looks gross to most of you lol). I'm getting a Complie error that says "Next without For". I'm not sure how I properly insert the looping code into the other looping code. Side note: I will need to do this again a few more times so I'm not sure if that changes the way the Next and For will work. I'm not going to "nest" another loop inside this one being nested, but I will be adding this same loop with some modifications to the other If Left(Cells(x, 1).... statements. Thanks in advance for any help you can provide!

Code:
Dim lr As Longlr = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lr
    If Left(Cells(x, 1), 20) = "PLAN_PAY_BY_PRODUCT_" Then Cells(x, 2) = "PlanPayByProductCode_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 24) = "DAILY_CHECKEFT_REISSUES_" Then Cells(x, 2) = "Daily_check_eft_reissues_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 22) = "DETAIL_GROUP_ACTIVITY_" Then Cells(x, 2) = "GroupActivityDetail_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 17) = "GROUP_ACTIVITY_2_" Then Cells(x, 2) = "GroupActivitySum2_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 23) = "GROUP_ACTIVITY_SUMMARY_" Then Cells(x, 2) = "GroupActivitySum1_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 17) = "MoO_Overpayments_" Then Cells(x, 2) = "MOO_Overpayments_" & Format(Date, "MMDDYYYY")
    If Left(Cells(x, 1), 19) = "MoO_CLAIMS_EXTRACT_" Then
    
                                'This is what needs to be inserted
                                strFileName = myPath & "MoO_CLAIMS_EXTRACT_" & "*" & ".TXT"
                                TEMPstrFileName = Dir(strFileName, vbNormal)
                                fdFileName = myPath & TEMPstrFileName
                                Const strSearch = "EH10"
                                Dim strLine As String
                                Dim f As Integer
                                Dim blnFound As Boolean
                                f = FreeFile
                                Open fdFileName For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f]#f[/URL] 
                                Do While Not EOF(f)
                                    lngLine = lngLine + 1
                                    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f]#f[/URL] , strLine
                                    If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
                                    Cells(x, 2) = "PaidClaimsEH10_" & Format(Date, "MMDDYYYY") 'If EH10 is found within the document, then this should be the name of the document
                                        blnFound = True
                                        Exit Do
                                    End If
                                Loop
                                Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f]#f[/URL] 
                                If Not blnFound Then
                                Cells(x, 2) = "MOO_CLAIMS_" & Format(Date, "MMDDYYYY") 'If EH10 is NOT found within the document, then this should be the name of the document
                                End If




Next x
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

igold

Well-known Member
Joined
Jul 8, 2014
Messages
3,017
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, I have not tested the code except to make the changes that will enable it to compile and to clean up your indentation which helps keep track of the If's and End If's

I declared a few variables that were not declared, but the main point is, I inserted an "End If" (highlighted in red) where I thought it needed to go to enable the code to compile.

As previously stated, I don't know if the code is going to do what you intend but at least you will be able to run it and find out...

Code:
Sub test()


    Dim lr As Long, x As Long, lngLine As Long
    Dim myPath As String, TEMPstrFileName As String, fdFileName As String, strFileName As String
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lr
        If Left(Cells(x, 1), 20) = "PLAN_PAY_BY_PRODUCT_" Then Cells(x, 2) = "PlanPayByProductCode_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 24) = "DAILY_CHECKEFT_REISSUES_" Then Cells(x, 2) = "Daily_check_eft_reissues_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 22) = "DETAIL_GROUP_ACTIVITY_" Then Cells(x, 2) = "GroupActivityDetail_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 17) = "GROUP_ACTIVITY_2_" Then Cells(x, 2) = "GroupActivitySum2_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 23) = "GROUP_ACTIVITY_SUMMARY_" Then Cells(x, 2) = "GroupActivitySum1_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 17) = "MoO_Overpayments_" Then Cells(x, 2) = "MOO_Overpayments_" & Format(Date, "MMDDYYYY")
        If Left(Cells(x, 1), 19) = "MoO_CLAIMS_EXTRACT_" Then
        'This is what needs to be inserted
            strFileName = myPath & "MoO_CLAIMS_EXTRACT_" & "*" & ".TXT"
            TEMPstrFileName = Dir(strFileName, vbNormal)
            fdFileName = myPath & TEMPstrFileName
            Const strSearch = "EH10"
            Dim strLine As String
            Dim f As Integer
            Dim blnFound As Boolean
            f = FreeFile
            Open fdFileName For Input As #f
                Do While Not EOF(f)
                    lngLine = lngLine + 1
                    Line Input #f, strLine
                    If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
                        Cells(x, 2) = "PaidClaimsEH10_" & Format(Date, "MMDDYYYY") 'If EH10 is found within the document, then this should be the name of the document
                        blnFound = True
                        Exit Do
                    End If
                Loop
            Close #f
[COLOR=#ff0000]        End If[/COLOR]
        If Not blnFound Then
            Cells(x, 2) = "MOO_CLAIMS_" & Format(Date, "MMDDYYYY") 'If EH10 is NOT found within the document, then this should be the name of the document
        End If
    Next x
    
End Sub
I hope this helps...
 
Last edited:

Forum statistics

Threads
1,170,959
Messages
5,872,995
Members
432,955
Latest member
minhnhat2504

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top