Compile error: Invalid or unqualified reference and For Loop Doesn't Loop

[Pookiemeister]

I have a userform with six option buttons and and three textboxes. I would like the code to loop through column G until it finds the first value that equals the value of textbox 3 then offset back three cells and check if that value equals the value in textbox1. Whether it contains the value or not, a message box will appear displaying a message stating yes it matches or no it does not match. This is for test purposes only. If it does not equal then I need to FindNext value and continue the loop.
The code is below:

Option Explicit


Dim sPath As String, sFile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer


Private Sub cmdbtnOpen_Click()

sPath = "C:\Production_Line"


Select Case True


Case Is = optSlit
sFile = sPath & "SDPF - LINE 1 (SLAT).xlsx"
Case Is = optMann
sFile = sPath & "SDPF - LINE 2A.xlsx"
Case Is = optCob
sFile = sPath & "SDPF - LINE 3.xlsx"
Case Is = optIA
sFile = sPath & "SDPF - LINE 4.xlsx"
Case Is = optMann5
sFile = sPath & "SDPF - LINE 5A.xlsx"
Case Is = optPouch
sFile = sPath & "SDPF - LINE 6.xlsx"
End Select


Select Case True
Case Is = optSlit
Worksheets("SDPF - LINE 1").Activate
Case Is = optMann
Worksheets("SDPF - LINE 2A").Activate
Case Is = optCob
Worksheets("SDPF - LINE 3").Activate
Case Is = optIA
Worksheets("SDPF - LINE 4").Activate
Case Is = optMann5
Worksheets("SDPF - LINE 5A").Activate
Case Is = optPouch
Worksheets("SDPF - LINE 6").Activate
End Select




Set wb = Workbooks.Open(sFile)
Unload Me


Set rng = ActiveSheet.Range("G:G").Find(Me.TextBox3.Value)
Debug.Print ActiveSheet.Name
Debug.Print rng.Address
Debug.Print rng.Offset(, -3).Value

For i = 1 To rng.Rows.Count
Debug.Print rng.Cells(RowIndex:=i, columnindex:="G").Value
If rng.Offset(, -3).Value = TextBox1.Value Then
MsgBox "You have found a match", vbCritical, "Match Found"
Else
MsgBox "You have NOT found a match", vbCritical, "Match Not Found"
Set rng = .FindNext(rng)
End If
Next i


End Sub

As the title states, I am getting a Compile error: Invalid or unqualified reference on the following line of code:

Set rng = .FindNext(rng)

The .FindNext(rng) is highlighted in blue.


Thank You

 

[Eric W]

I'm not sure if you intended to open a new thread or not, but this new question is appended to your old thread. The short answer to your question is that you are getting the "unqualified reference" error because you have an unqualified reference on that line. More specifically, when you start a method with a period (.FindNext), you have to have already specified what object the method applies to with a With statement. For example:

With Workbooks("MyWorkbook.xls").Worksheets("Grand Totals")
    .Cells(1, "A") = 1
    .Cells(2, "B") = 2
End With

You can see that it can save a lot of typing. But when you used the period without the With, the compiler has no idea what you want to do.


Now the bigger question is what are you trying to do? In looking over your code, I can't figure out what you want to do, even assuming I know what you want to find. I'd suggest looking over the code on this page:

https://docs.microsoft.com/en-us/office/vba/api/excel.range.findnext

which shows how to find all matching values in a range.

EDIT:
Moved from duplicate posting

 

[Joe4]

I doubt that it ever finds any files to open, as it appears you are missing a slash between your file path and file name.
I think you need:

sPath = "C:\Production_Line[COLOR=#ff0000]\[/COLOR]"