Why is my script stopping short on this True/False to match two values?

Question

I have an application built where a dialogue box pops up, asks about two values in two columns in Sheet 2. If they both match, a msg box pops up, and then other instances other msgs. When I type in a job code, it matches to that job code, but that code could have multiple cost-centers aligned. My code is stopping at the first row where that match happens, but doesn't loop through all rows to see if it can find the cost-center. It returns a "found but not eligible for this cost-center" message, but it doesn't finish looping to truly be able to tell or not.

What do I need to change here to make sure it doesn't prematurely trigger that msg?

It's saying rFound is finding the job code, but that job code has 4 rows for each eligible cost center spanning rows 211-14. It stops at row 211, then doesn't try looking through the other 3 rows where rFound is matched. Code:

Option Explicit

Sub tgr()

Dim rFound As Range
Dim lJobCode As String
Dim lFLSA As String
Dim lCC As String
Dim sFirst As String
Dim sResults As String
Dim sh As Worksheet
Dim matched As Boolean
Dim Y As Integer, Z As Integer

lJobCode = Application.InputBox("Please provide a job code", "Job Code", Type:=2)
lCC = Application.InputBox("Please enter in a cost-center", "CC", Type:=2)

If lJobCode = "False" Or lCC = "False" Then Exit Sub 'Pressed cancel

Set sh = Sheets("Sheet1")

With ThisWorkbook.Worksheets("Sheet2").Columns("A")

    Set rFound = .Find(lJobCode, .Cells(.Cells.Count), xlValues, xlWhole)

    If Not rFound Is Nothing Then

        If ThisWorkbook.Worksheets("Sheet2").Cells(rFound.Row, 3).Value = lCC Then

            matched = True

            If rFound.Offset(, 4).Value = "Exempt" And Y = 0 Then
                MsgBox "Exempt roles may be eligible for schedule pay allowance."
                Y = 1

            Exit Sub 'if criteria is met, display above msgbox and then exit sub after user clicks ok or cancel

            End If

            If rFound.Offset(, 5).Value = "Eligible - Employee Level" And Z = 0 Then
                MsgBox "This job is only eligible at the employee level."
                Z = 1

            Exit Sub

            End If

            MsgBox "Job Code (" & lJobCode & ") is eligible for this cost-center."

        End If

        If Not matched Then MsgBox "Job Code (" & lJobCode & ") found, but not eligible for this cost-center."

    Else
        MsgBox "Job Code (" & lJobCode & ") not eligible."
    End If

End With

End Sub

Is this the correct code? If yes, where is the loop in your code? I see no `Do .... Loop` or `For ... Next` — cybernetic.nomad, May 17 '19 at 19:17
I just realized it's not looping, but it's doing a search all, so why is that not working either way? What would you suggest I do differently? — nick lanta, May 17 '19 at 19:26
I think it's that it's only picking up the first column of `rFound` and isn't starting a search in Column C past the first instance of finding the `lJobCode` in column A — nick lanta, May 17 '19 at 19:35
Step through the code with `F8` - that should help you find out where/why the script stops. (You can also hover over variables with the mouse and it'll show you their current value, which may help. Also there's the immediate window and watch window you can use) — BruceWayne, May 17 '19 at 21:02
It's not looping because you haven't coded it to loop. It's doing exactly what it's coded to do. If you want it to find all matching values, then you'll need to code for that. See [How to loop Range.Find in VBA?](https://stackoverflow.com/questions/46215911/how-to-loop-range-find-in-vba) — tigeravatar, May 17 '19 at 21:07
It stops right before the `matched = true`and shows the `lJobCode` variable as the first row where that value appears in the sheet, not necessarily the row I need it to. Doesn’t make any sense why this isn’t taking into account the input inneed to find in column c, too — nick lanta, May 17 '19 at 21:08
@tigeravatar so I can set it to loop through both ranges to make sure it matches to the correct row? I’ll look through that thread on Monday, thank you! — nick lanta, May 17 '19 at 21:16
@tigeravatar So after looking at my script, I don't need it to loop. I have this line to do what I need to match to the specific row I'm looking for: `If ThisWorkbook.Worksheets("Sheet2").Cells(rFound.Row,3).Value = lCC Then` should be all I need to trigger the right msgbox, but it's still not doing that second part to find the row with the `lCC` I inputted in the second dialogue box. Why is that? — nick lanta, May 20 '19 at 12:55

score 1 · Accepted Answer · answered May 20 '19 at 14:31

You have a lot of unused variables in your code (maybe its simplified from what you're actually trying to do?) so I removed all the extra stuff. Based on your comments and the original code provided, here's what I believe you're actually after:

Sub tgr()

    Dim wsData As Worksheet
    Dim rFound As Range
    Dim lJobCode As String
    Dim lCC As String
    Dim sFirst As String
    Dim matched As Boolean

    lJobCode = Application.InputBox("Please provide a job code", "Job Code", Type:=2)
    If lJobCode = "False" Then Exit Sub 'Pressed cancel
    lCC = Application.InputBox("Please enter in a cost-center", "CC", Type:=2)
    If lCC = "False" Then Exit Sub 'Pressed cancel

    matched = False
    Set wsData = ThisWorkbook.Worksheets("Sheet2")
    Set rFound = wsData.Columns("A").Find(lJobCode, wsData.Cells(wsData.Rows.Count, "A"), xlValues, xlWhole)

    If Not rFound Is Nothing Then
        sFirst = rFound.Address
        Do
            If rFound.Offset(, 2).Value = lCC Then
                matched = True

                If rFound.Offset(, 4).Value = "Exempt" Then
                    MsgBox "Exempt roles may be eligible for schedule pay allowance."
                    Exit Sub 'if criteria is met, display above msgbox and then exit sub after user clicks ok or cancel
                End If

                If rFound.Offset(, 5).Value = "Eligible - Employee Level" Then
                    MsgBox "This job is only eligible at the employee level."
                    Exit Sub
                End If

                MsgBox "Job Code (" & lJobCode & ") is eligible for this cost-center."
                Exit Sub
            End If
            Set rFound = wsData.Columns("A").FindNext(rFound)
        Loop While rFound.Address <> sFirst
        If Not matched Then MsgBox "Job Code (" & lJobCode & ") found, but not eligible for this cost-center."
    Else
        MsgBox "Job Code (" & lJobCode & ") not eligible."
    End If

End Sub

wow... that worked flawlessly.. What are the main differences between my code and yours? I'll look at it side to side, but thank you a lot for this, exactly what I'm looking for. — nick lanta, May 20 '19 at 14:39
The main difference is the implementation of the Range.Find loop. I already linked you a reference to it, please read it. — tigeravatar, May 20 '19 at 14:41
Hey, I have a quick question. Let's say the row for `lJobCode` and `lCC` is found, is how would I put in the `MsgBox` the value of that Column H for that row in the msg? Something like `MsgBox "Job Code (" & lJobCode & ") is eligible for this (" & Column H Value for this row & ") cost-center."` — nick lanta, May 29 '19 at 13:59
@nicklanta `MsgBox "Job Code (" & lJobCode & ") is eligible for this (" & rFound.Offset(, 7).Value & ") cost-center."` — tigeravatar, May 29 '19 at 14:57

Why is my script stopping short on this True/False to match two values?

1 Answers1