Results 1 to 9 of 9

Thread: Find two values, do something, then findnext instance of both, do some more things

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,695
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find two values, do something, then findnext instance of both, do some more things

    folks, i am working through a reformatting problem in another thread and came to a new problem along the way.

    Find and Findnext:

    in my active range, i set two variables to find.

    Code:
    Set c = .Find("DEBT2.P33", LookIn:=xlValues)    Set d = .Find("Previous Months Adjustments:", LookIn:=xlValues)
    from the positions in which these strings are found, i offset to gather certain values. These values are then pasted to a new sheet.

    the code then resets (findsnext) the variables as below:
    Code:
    Set c = .FindNext(c)            Set d = .FindNext(d)
    the theory being that the code then finds the next instance of the c variable and the next instance of the d variable, offsetting from those addresses to gather the next lot of values. this continues until the loop has gathered data from every page.

    However, on resetting c, which should then find the next instance of "DEBT2.P33" in my active sheet, the variable becomes "Previous Months Adjustments:", which is the d variable.

    Can you have more than one Find/FindNext pair in a macro?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,211
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    This way it is not possible:

    Code:
    Sub test2()
      Dim c As Range, r As Range, d As Range
      Set r = Sheets("sheet1").Range("A:A")
      Set c = r.Find("DEBT2.P33", , xlValues, xlWhole)
      Set d = r.Find("Previous Months Adjustments:", , xlValues, xlWhole)
      Set c = r.FindNext(c)
      Set d = r.FindNext(d)
      MsgBox c.Row
      MsgBox d.Row
    End Sub
    -------------------
    But in this way yes:

    Code:
    Sub test()
      Dim c As Range, r As Range, d As Range
      Set r = Sheets("sheet1").Range("A:A")
      Set c = r.Find("DEBT2.P33", , xlValues, xlWhole)
      Set c = r.FindNext(c)
      MsgBox c.Row
      Set d = r.Find("Previous Months Adjustments:", , xlValues, xlWhole)
      Set d = r.FindNext(d)
      MsgBox d.Row
    End Sub
    -------------------

    What do you need exactly?
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,695
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    so do one find/find next, get my values from the adjacent cells, and then do a second find/find next and get the second lot of values from the adjacent cells?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,211
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    Quote Originally Posted by ajm View Post
    so do one find/find next, get my values from the adjacent cells, and then do a second find/find next and get the second lot of values from the adjacent cells?
    Take a test and check if it is what you need.
    Otherwise, explain with examples what you need to look for and what you need to get.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,695
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    Code:
    Sub test()
    
    
        Dim CurrMth As String, Class As String, Beds As String, CMAmtRsd As String, PMBedDays As String, PMAmtRsd As String
    
    
        Dim Rng As Range, firstAddress as Range
        Dim c As Range, d As Range
        Dim pasteCell As Range
        Dim DestSht As Worksheet
    
    
    
    
       
        Set DestSht = Worksheets("AmtsRaised") '///will paste values to this sheet
            
        CurrMth = Trim(Right(ActiveSheet.Range("c2"), 6)) '///current calendar month
       
        Set Rng = ActiveSheet.Range("a1", Range("a1048576").End(xlUp)) '///where is the data that has been imported from ERP
     
            Set c = Rng.Find("DEBT2.P33", LookIn:=xlValues) '/// what do we look for first
                   
            If Not c Is Nothing Then
                firstAddress = c.Address '///  if value exists, assign variable name to its address (location on page)
            
                Do '///what actions do we want to perform
    
    '///assign variable names to desired values. these are offset from the Find value
                    CMClass = Trim(Right(c.Offset(2, 0).Value, Len(c.Offset(2, 0).Value) - InStr(c.Offset(2, 0).Value, ":")))
                    Beds = Trim(Right(c.Offset(8, 0).Value, Len(c.Offset(8, 0).Value) - InStr(c.Offset(8, 0).Value, ":")))
                    CMAmtRsd = Trim(c.Offset(10, 7).Value)
            
                    
                    With DestSht
                                    
                        Set pasteCell = .Range("b1048576").End(xlUp).Offset(1, 0) '///find the first empty cell in Col b
                    
                        '/// paste the variables adjacent to first available cell in Col B
                        pasteCell.Offset(0, -1).Value = "Current Month"
                        pasteCell.Value = CurrMth
                        pasteCell.Offset(0, 1).Value = CMBeds
                        pasteCell.Offset(0, 2).Value = CMClass
                        pasteCell.Offset(0, 3).Value = CMAmtRsd
    
    
                    End With
                          
     '///find next instance of what we are looking for
                    Set c = Rng.FindNext(c)
    
    
                '///loop through this process until there are no more values to find
                Loop While Not c Is Nothing And c.Address <> firstAddress
    
    
            End If
    
    '///now look for second value we need
                Set d = Rng.Find("Previous Months Adjustments:", LookIn:=xlValues)
            
                If Not d Is Nothing Then
                    firstAddress = d.Address  '///  if value exists, assign variable name to its address (location on page)
            
                    Do   '///find the values we want relative to d 
                        PMClass = Trim(Right(d.Offset(2, 0).Value, Len(d.Offset(2, 0).Value) - InStr(d.Offset(2, 0).Value, ":")))
                        PMBedDays = Trim(Right(d.Offset(3, 0).Value, Len(d.Offset(3, 0).Value) - InStr(d.Offset(3, 0).Value, ":")))
                        PMAmtRsd = Trim(d.Offset(5, 7).Value)
                 
                        '
                        '///paste these variables to the bottom of the list on the DestSht
         
                        With DestSht
                            Set pasteCell = .Range("b1048576").End(xlUp).Offset(1, 0)
                          
                            'Copy and paste
                            pasteCell.Offset(0, -1).Value = "Previous Months Adjustments:"
                            pasteCell.Value = CurrMth
                            pasteCell.Offset(0, 1).Value = PMBedDays
                            pasteCell.Offset(0, 2).Value = PMClass
                            pasteCell.Offset(0, 3).Value = PMAmtRsd
         
                        End With
                        
            
                        Set d = Rng.FindNext(d) '///next instance
                
                    Loop While Not d Is Nothing And d.Address <> firstAddress
                End If
            
        End Sub
    Dante, this is my code in full. it loops through a data extract which has been imported from our ERP and when it finds the search values, it returns adjacent values. i originally posted the query here: https://www.mrexcel.com/forum/excel-...a-extract.html.
    Last edited by ajm; Aug 28th, 2019 at 01:18 AM.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    I don't quite understand what you want but maybe this example could help:
    The code search for X & Y.
    You can see the loop give you the address of X & Y found incrementally (see debug result).

    Code:
    Sub doubleFind2()
    'https://www.mrexcel.com/forum/excel-questions/1108234-find-two-values-do-something-then-findnext-instance-both-do-some-more-things.html
    
    Dim c As Range, d As Range, f As Range, z As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
        Set f = Range("A1", Cells(Rows.count, "A").End(xlUp))
        Set z = Range("A:A").Find("X", LookIn:=xlValues, lookAt:=xlPart, Searchorder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If z Is Nothing Then Exit Sub
        i = z.Row
    
        Do
            Set c = f.Find(What:="X", LookIn:=xlValues, After:=Cells(i, "A"), SearchDirection:=xlNext)
            Set d = f.Find(What:="Y", LookIn:=xlValues, After:=Cells(c.Row, "A"))
                    
            If d Is Nothing Then Exit Do
            If d.Row < c.Row Then Exit Do
                'do something here
                Debug.Print c.Address & " : " & d.Address
                    
            i = d.Row
        
        Loop Until z.Row = c.Row
    
    Application.ScreenUpdating = True
    
    End Sub

    Example:
    Excel 2013 32 bit
    A
    1
    1
    2
    X
    3
    Y
    4
    4
    5
    X
    6
    6
    7
    7
    8
    8
    9
    9
    10
    10
    11
    Y
    12
    12
    13
    13
    14
    X
    15
    15
    16
    16
    17
    17
    18
    Y
    19
    19
    20
    20
    Sheet: Sheet2

    DEBUG RESULT:

    $A$2 : $A$3
    $A$5 : $A$11
    $A$14 : $A$18

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,211
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    Do you have problems with your code?
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,695
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    Quote Originally Posted by Akuini View Post
    I don't quite understand what you want but maybe this example could help:
    The code search for X & Y.
    You can see the loop give you the address of X & Y found incrementally (see debug result).

    Code:
    Sub doubleFind2()
    'https://www.mrexcel.com/forum/excel-questions/1108234-find-two-values-do-something-then-findnext-instance-both-do-some-more-things.html
    
    Dim c As Range, d As Range, f As Range, z As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
        Set f = Range("A1", Cells(Rows.count, "A").End(xlUp))
        Set z = Range("A:A").Find("X", LookIn:=xlValues, lookAt:=xlPart, Searchorder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If z Is Nothing Then Exit Sub
        i = z.Row
    
        Do
            Set c = f.Find(What:="X", LookIn:=xlValues, After:=Cells(i, "A"), SearchDirection:=xlNext)
            Set d = f.Find(What:="Y", LookIn:=xlValues, After:=Cells(c.Row, "A"))
                    
            If d Is Nothing Then Exit Do
            If d.Row < c.Row Then Exit Do
                'do something here
                Debug.Print c.Address & " : " & d.Address
                    
            i = d.Row
        
        Loop Until z.Row = c.Row
    
    Application.ScreenUpdating = True
    
    End Sub

    Example:
    Excel 2013 32 bit
    [COLOR=#FFFFFF ]A[/COLOR]
    [COLOR=#FFFFFF ]1[/COLOR]
    1
    [COLOR=#FFFFFF ]2[/COLOR]
    X
    [COLOR=#FFFFFF ]3[/COLOR]
    Y
    [COLOR=#FFFFFF ]4[/COLOR]
    4
    [COLOR=#FFFFFF ]5[/COLOR]
    X
    [COLOR=#FFFFFF ]6[/COLOR]
    6
    [COLOR=#FFFFFF ]7[/COLOR]
    7
    [COLOR=#FFFFFF ]8[/COLOR]
    8
    [COLOR=#FFFFFF ]9[/COLOR]
    9
    [COLOR=#FFFFFF ]10[/COLOR]
    10
    [COLOR=#FFFFFF ]11[/COLOR]
    Y
    [COLOR=#FFFFFF ]12[/COLOR]
    12
    [COLOR=#FFFFFF ]13[/COLOR]
    13
    [COLOR=#FFFFFF ]14[/COLOR]
    X
    [COLOR=#FFFFFF ]15[/COLOR]
    15
    [COLOR=#FFFFFF ]16[/COLOR]
    16
    [COLOR=#FFFFFF ]17[/COLOR]
    17
    [COLOR=#FFFFFF ]18[/COLOR]
    Y
    [COLOR=#FFFFFF ]19[/COLOR]
    19
    [COLOR=#FFFFFF ]20[/COLOR]
    20
    Sheet: Sheet2

    DEBUG RESULT:

    $A$2 : $A$3
    $A$5 : $A$11
    $A$14 : $A$18
    Akuini, thanks for your code. I actually got mine to work but yours looks a lot tidier so will play around with it when i have a bit of time up my sleeve.

    Quote Originally Posted by DanteAmor View Post
    Do you have problems with your code?
    Dante, no problem with the code i posted.

    I have actually added in a workbooks.open snippet at the start so that the original export file can be found, opened, and parsed. It runs a little slower now. would you be interested in seeing it to see if you can identify anything i have done inefficiently?

    As to my posting the final code above, I like to post what worked for me in the end so that the thread has closure for anyone else looking for something similar. There are so many threads on the different forums where the OP asks their question, and someone responds, and then nothing.
    Last edited by ajm; Sep 1st, 2019 at 07:42 PM.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,211
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find two values, do something, then findnext instance of both, do some more things

    you can filter by this value "DEBT2.P33" copy and paste in the target sheet.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •