Continuing A Loop When Criteria Is Prematurely Met

[Ark68]

If I may, let me revisit a chunk of code that I was working through in this post in which I am encountering a new issue.
Please consider this code:

Sub signatures(srow As Integer, pnum As String, fac2 As String, nrec As Long, st As Variant)
   
    'signatures are assigned based on that facility's assigned crew and eligible shift
    'this assignmnet, whether a signature is required or not, determines the crew assignemnt of this booking
    'srow = the row on the master worksheet being analysed
   
    Dim cd_rrow As Integer 'the row that the current record resides in CORE_DATA
    mbevents = False
    Stop
 
    'find the row (cd_rrow) in CORE_DATA for which the unique currect booking resides (permit#, facility, and start time)
   
    v1 = Application.WorksheetFunction.Match(pnum, ws_cd.Columns(17), 0) 'returns row number of first match of pnum in column 17 eg 2
    v2 = Application.WorksheetFunction.Match(fac2, ws_cd.Columns(6), 0)    'returns row number of first match of pnum in column 6 eg 2
    'v3
    Dim V As Variant, v3 As Variant
    V = ws_cd.Columns(2).Value
    'For iv = 1 To UBound(V, 1)
    For iv = 2 To nrec
        V(iv, 1) = Round(V(iv, 1), 3)
    Next iv
    v3 = Application.Match(Round(st, 3), V, 0)
    If Not IsError(v3) Then
        Debug.Print v3
    Else
        MsgBox "no match in column B"
    End If
    If v1 = v2 And v1 = v3 Then
        cd_rrow = v1
        MsgBox "The FIRST row in which all three criteria are satisfied is row : " & cd_rrow
    Else
        MsgBox "There is no row that match all three criteria."
        Stop
    End If
'
'    Stop
   
     ..... unrelated code

    mbevents = True
   
End Sub

The area in orange is where I'm struggling. The idea is to find the row in which V1 = V2 = V3 from the data in ws_cd within the number of rows in the database defined by variable nrec. There will ALWAYS be ONE match within the data, but each row needs to be stepped through to find it. It can't assume the first instance is the one it's supposed to catch.

In my testing, the first record is matched at row two. A match of pnum in row 2, a match of fac 2 in row 2 and the start time (st) in row 2 = .375 (9:00A). A match! V1=V2=V3 and the appropriate message is displayed.

We step into the next record at row 3. A match of pnum in row 5, a match in fac2 in row 5 but the value of st (.375) is first encountered in row 2! V1=V2<>V3. Their is a message that there is no row matching all three criteria. This is not true. If it didn't accept V3=2 (the first instance of 9:00A) and kept going, it would have discovered that row 5 also contains the value of 9:00A. This would have triggered V1=V2=V3

So, my question is, how do I keep going to find the match. I have to revisit the "Else" code in orange, but honestly, I'm stumped. I'm not even sure I explained myself right, so if not, please ask for clarification.

 

[6StringJazzer]

Your description uses the phrase "We step into the next record at row 3" but the only thing you do within a loop here is fill the array V. Your description with phrases like "and kept going" and "how do I keep going to find the match" and your title with "Continuing a loop" sounds like you are looping through all of the rows, but that is not what the code is actually doing. So this mismatch between the description and the code makes it difficult to understand what you intend for this code to do.

You determine v1 by finding the first match of pnum in column 17
You determine v2 by finding first match of fac in column 6 [your comment actually says pnum but I think that is an error]
You determine v3 by searching for the first match of a rounded st in the rounded values of column 2

So you find the three rows where the first match occurs for each of these values independently. Then you check to see if they are all the same row. The problem with this is there is no logic to find a row where all three of these are matched on the same row, only three independent matches.

Based only on the code you presented I would propose this instead, which finds the first row where all three match. I compiled it, however, I am not able to test it without your file. Consider sharing your file with a (free) cloud service like Dropbox.

I noted that you have an undeclared variable mbevents. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Sub signatures(srow As Integer, pnum As String, fac2 As String, nrec As Long, st As Variant)
  
    'signatures are assigned based on that facility's assigned crew and eligible shift
    'this assignmnet, whether a signature is required or not, determines the crew assignemnt of this booking
    'srow = the row on the master worksheet being analysed
  
    Dim cd_rrow As Integer 'the row that the current record resides in CORE_DATA
    Dim mbevents As Boolean
    Dim FoundPnum As Range
    Dim FirstFoundPnum As Range

   mbevents = False
  
   ' Find pnum matches. For each match, check for a match on fac2 and st on the same row.
   Set FoundPnum = ws_cd.Columns(17).Find(what:=pnum, lookat:=xlWhole, LookIn:=xlValues)
   If FoundPnum Is Nothing Then
      MsgBox "Did not find pnum " & pnum
   Else
  
      Set FirstFoundPnum = FoundPnum
      Do Until (fac2 = ws_cd.Cells(FoundPnum.Column, 6) And Round(st, 3) = Round(ws_cd.Cells(FoundPnum.Column, 2))) Or _
                FoundPnum.Address = FirstFoundPnum.Address
         Set FoundPnum = ws_cd.Columns(17).FindNext(after:=FoundPnum)
      Loop
     
      If fac2 = ws_cd.Cells(FoundPnum.Column, 6) And Round(st, 3) = Round(ws_cd.Cells(FoundPnum.Column, 2)) Then
          cd_rrow = FoundPnum.Row
          MsgBox "The FIRST row in which all three criteria are satisfied is row : " & cd_rrow
      Else
          MsgBox "There is no row that match all three criteria."
      End If
     
   End If
  
     '..... unrelated code

    mbevents = True
  
End Sub

 

[Ark68]

That's great Jeff, thank you for your suggestion.
Sometimes I find that the best way of explaining things is to know the jargon of coding. Clearly it's a language that if was universally understood as fluently as the experts can use it, it would make a world of difference. So, I extend my apologies for the lack of clarity due to my stumbling of appropriate terminolgy. I didn't quite know how to present what I was needing to do in terms that would define it appropriately. Part of that code was offered up by another fine member (the V3 process) and I never quite was able to interpret what it was doing.

Although I found a work around while waiting which is a much more basic and likely long winded method, out of respect for your help and the others up to this point, I am certainly going to revisit it as a learning opportunity!

I would more than certainly share my application ... what better means of explanation than to allow someone to experience it. However, it is several thousand lines of code, relying on several network accessible secondary workbooks of reference data full of sensitive data. It would be throwing errors all over the place, and it would be difficult to desensitize the data to provide accurate functionality.

I have declared a great deal of my variables, mbevents being one, publicly in my "application"'s initialization code.

 

[6StringJazzer]

Another method I thought of was just looping through all the rows until I found a triple match. It easy to understand, but I suspect the Find method will be a lot faster. It is similar to using MATCH but is specific to the VBA object model for Excel and returns a range instead of a row number.

...several thousand lines of code, relying on several network accessible secondary workbooks of reference data full of sensitive data. It would be throwing errors all over the place, and it would be difficult to desensitize the data to provide accurate functionality.....

Yeah, OK, let's not do that.

I have declared a great deal of my variables, mbevents being one, publicly in my "application"'s initialization code.

What got my attention here was "a great deal of ... variables." I am not going to suggest that your redesign something that is working, but in general, using public variables to pass data around between modules is what is known as common data coupling and can result in complicated designs and exotic bugs that are hard to diagnose. It is better to pass data to subs through arguments. There are certainly exceptions, particularly in VBA, but something to think about in your next project.

 

[Ark68]

Hey Jeff, that's great information shared! Thanks.
The alternate method I tried as mentioned while waiting was basically what you had suggested, mind you without using FIND (looks like another function to research!). I mentioned my version was messy ... just a bunch of nested loops which quite frankly is a worm's nest lol.

I've always been cognizant of declaring too many public variables. I just fear down the road memory erors. I'm trying very hard to structure my application to avoid too many, and as you said, to work more with passing arguments. Sometimes where I struggle to do that, I will simply put a value in a cell on a worksheet and refer to that. Typical newbie move.

I have been working on this "application" for I bet 8 years now. It's been a work in progress with learned improvements made all through the process. I am not a computer programmer by any stretch of the imagination. I have gotten pretty far predominently with the patient help of the great people here at Mr. Excel. Google has helped, but only when one knows what to search for. Not knowing often leads to more problems in adaptation lol. I have a very cumbersome task to do at work using multiple Excel workbooks of data. My employer charged me with this task and has no care as to how complicated the process is. I learned that macros can save a lot of repetitive work, so I started long ago just working with simple recorded macos. But in time, those became too limited. So I embarked on tweaking existing macros, learning then that what I was doing was actually programming in VBA. I never imagined! So, as much as I find it challenging, and enjoyable, I really wish I had years of formal education and time to properly self educate.

So, 8 years later, my employer is in the stages now of m igrating much of our operation to tablets, and the cloud. I'm working more now on an iPad with simple Excel basic Excel worksheets than I am at my desktop now, and the task is even more complicated. And we all know that VBA, even macros, are not of any use on Android or iOS platforms. I fear all my work has been in vain, and now, I have to see if I can convince myself it's I'm capable of learning a new approach ... Javascript, or some web-based database (which is likely Javascript). I fear that may be a pipedream for the uninitiated.

Thanks Jeff!!!

 

[6StringJazzer]

There is a new scripting language that Microsoft is rolling out for the web versions of Office. Unfortunately it won't work on desktop versions, and VBA won't work on web versions. I don't know why they don't universally support one or the other. I have just learned about this a couple of days ago and I don't know if it is supported using a mobile device.