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

[ajm]

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.

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:

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?

 

[DanteAmor]

This way it is not possible:

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:

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?

 

[ajm]

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?

 

[DanteAmor]

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.

 

[ajm]

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-questions/1108104-reformatting-data-extract.html.

 

[Akuini]

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).

[FONT=lucida console][color=Royalblue]Sub[/color] doubleFind2()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1108234-find-two-values-do-something-then-findnext-instance-both-do-some-more-things.html[/color][/i]

[color=Royalblue]Dim[/color] c [color=Royalblue]As[/color] Range, d [color=Royalblue]As[/color] Range, f [color=Royalblue]As[/color] Range, z [color=Royalblue]As[/color] Range
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color]

Application.ScreenUpdating = [color=Royalblue]False[/color]
    [color=Royalblue]Set[/color] f = Range([color=brown]"A1"[/color], Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp))
    [color=Royalblue]Set[/color] z = Range([color=brown]"A:A"[/color]).Find([color=brown]"X"[/color], LookIn:=xlValues, lookAt:=xlPart, Searchorder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=[color=Royalblue]False[/color], SearchFormat:=[color=Royalblue]False[/color])
    [color=Royalblue]If[/color] z [color=Royalblue]Is[/color] [color=Royalblue]Nothing[/color] [color=Royalblue]Then[/color] [color=Royalblue]Exit[/color] [color=Royalblue]Sub[/color]
    i = z.Row

    [color=Royalblue]Do[/color]
        [color=Royalblue]Set[/color] c = f.Find(What:=[color=brown]"X"[/color], LookIn:=xlValues, After:=Cells(i, [color=brown]"A"[/color]), SearchDirection:=xlNext)
        [color=Royalblue]Set[/color] d = f.Find(What:=[color=brown]"Y"[/color], LookIn:=xlValues, After:=Cells(c.Row, [color=brown]"A"[/color]))
                
        [color=Royalblue]If[/color] d [color=Royalblue]Is[/color] [color=Royalblue]Nothing[/color] [color=Royalblue]Then[/color] [color=Royalblue]Exit[/color] [color=Royalblue]Do[/color]
        [color=Royalblue]If[/color] d.Row < c.Row [color=Royalblue]Then[/color] [color=Royalblue]Exit[/color] [color=Royalblue]Do[/color]
            [i][color=seagreen]'do something here[/color][/i]
            Debug.Print c.Address & [color=brown]" : "[/color] & d.Address
                
        i = d.Row
    
    [color=Royalblue]Loop[/color] [color=Royalblue]Until[/color] z.Row = c.Row

Application.ScreenUpdating = [color=Royalblue]True[/color]

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Example:
Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

5​

[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

6​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

7​

[/td][td]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

8​

[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

9​

[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

10​

[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

11​

[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

12​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

13​

[/td][td]

13​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

14​

[/td][td]X[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

15​

[/td][td]

15​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

16​

[/td][td]

16​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

17​

[/td][td]

17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

18​

[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

19​

[/td][td]

19​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

20​

[/td][td]

20​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

DEBUG RESULT:

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

 

[DanteAmor]

Do you have problems with your code?

 

[ajm]

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).

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] doubleFind2()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1108234-find-two-values-do-something-then-findnext-instance-both-do-some-more-things.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, d [COLOR=Royalblue]As[/COLOR] Range, f [COLOR=Royalblue]As[/COLOR] Range, z [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]Set[/COLOR] f = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
    [COLOR=Royalblue]Set[/COLOR] z = Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"X"[/COLOR], LookIn:=xlValues, lookAt:=xlPart, Searchorder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
    [COLOR=Royalblue]If[/COLOR] z [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    i = z.Row

    [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]Set[/COLOR] c = f.Find(What:=[COLOR=brown]"X"[/COLOR], LookIn:=xlValues, After:=Cells(i, [COLOR=brown]"A"[/COLOR]), SearchDirection:=xlNext)
        [COLOR=Royalblue]Set[/COLOR] d = f.Find(What:=[COLOR=brown]"Y"[/COLOR], LookIn:=xlValues, After:=Cells(c.Row, [COLOR=brown]"A"[/COLOR]))
                
        [COLOR=Royalblue]If[/COLOR] d [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]If[/COLOR] d.Row < c.Row [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
            [I][COLOR=seagreen]'do something here[/COLOR][/I]
            Debug.Print c.Address & [COLOR=brown]" : "[/COLOR] & d.Address
                
        i = d.Row
    
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]Until[/COLOR] z.Row = c.Row

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Example:
Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]11[/COLOR]​

[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]12[/COLOR]​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]13[/COLOR]​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]14[/COLOR]​

[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]15[/COLOR]​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD]

16​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]18[/COLOR]​

[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]19[/COLOR]​

[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]20[/COLOR]​

[/TD]
[TD]

20​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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.

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.

 

[DanteAmor]

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