GO TO THE LAST DATA BASED ON 2 CELL DATA

[wndncg]

I have a working code just for 1 data but i need based on 2 data. ty mrexcel.

Dim Rng As Range
Set Rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set wsCellVal = Sheets("TEMPLATE")
Set wsFIN = Sheets("FIN")
cv = wsCellVal.Range("E1")
Columns("A").Find(cv, After:=Rng, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, _
SearchFormat:=False).Select
Application.Wait (Now + TimeValue("00:00:01"))
'wsFIN.Activate
'Call INSERT_ROW_SPECIFIC
'ActiveCell.EntireRow.Insert

 

[Alex Blakenburg]

So you are wanting the last row in Column A where
• the value in Column A = E1 and
• the value in Column B = A1.
on the Sheet Template.
Is that correct ?

 

[wndncg]

So you are wanting the last row in Column A where
• the value in Column A = E1 and
• the value in Column B = A1.
on the Sheet Template.
Is that correct ?

i will clear what i want to happen sir.
-The button is INSERT DM
-INSERT DM will run based on:
1. A1 that will find the last data based on E1
2. So in theory when user pressed the button the selection will go A6
i hope it clears sorry for bad engrish.

 

[cmowla]

So you are wanting the last row in Column A where
• the value in Column A = E1 and
• the value in Column B = A1.
on the Sheet Template.
Is that correct ?

That's exactly right. (Also, he wants VBA to go and select the match, if there is one. Otherwise do nothing.) And, as you can see, my code does that. I am confused as to why it doesn't work for him.

 

[Alex Blakenburg]

Your sample data only has agency in it so it is hard to tell whether our understanding is correct.
I thought and so did cmowla that the statement below meant you wanted to check the date (Col A) and the agency (Col B)

Its different because it now wants to find the last data if based on 2 cell value

Your last post is unclear.

1. A1 that will find the last data based on E1

1) Are we only matching the date or also the agency ?

2) Your example does indicate that when E1 has 01/01/2022 in it, you want the "LAST" row that contains 01/01/2022 is that correct ?

@cmowla's code is finding the "FIRST" row that contains it.

3) Also can you confirm whether you are getting the First row as a result OR are you getting a "Not Found" message ?

Can you please make sure you answer all 3 questions.

 

[wndncg]

Your sample data only has agency in it so it is hard to tell whether our understanding is correct.
I thought and so did cmowla that the statement below meant you wanted to check the date (Col A) and the agency (Col B)

Your last post is unclear.

1) Are we only matching the date or also the agency ?

2) Your example does indicate that when E1 has 01/01/2022 in it, you want the "LAST" row that contains 01/01/2022 is that correct ?

@cmowla's code is finding the "FIRST" row that contains it.

3) Also can you confirm whether you are getting the First row as a result OR are you getting a "Not Found" message ?

Can you please make sure you answer all 3 questions.

1. A1 is the basis to B then match with E1
2. Yes and select it
3. getting only not found message and does not go to selected area.

 

[Alex Blakenburg]

But you want the "last" row that matches, correct ?

 

[wndncg]

But you want the "last" row that matches, correct ?

yes

 

[Alex Blakenburg]

To try and fix the "Not Found" issue, if you change the struck out 2 lines in the code to the lines in blue does it find it and return the First time it finds it ?

If it does maybe @cmowla can then adjust the find direction to work from the bottom up to find the Last occurence.
I am login off for the night.

    Set LastCell = myRange.Cells(myRange.Cells.Count)
   Set FoundCell = myRange.Find(What:=search, after:=LastCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
    Set FoundCell = myRange.Find(What:=Format(search, .Range("A3").NumberFormat), after:=LastCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)

        Do Until FoundCell Is Nothing
            rowMatches = rowMatches & "," & FoundCell.Row
           Set FoundCell = myRange.Find(What:=search, after:=LastCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
            Set FoundCell = myRange.Find(What:=Format(search, .Range("A3").NumberFormat), after:=LastCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)

 

[cmowla]

@cmowla's code is finding the "FIRST" row that contains it.

It found all occurrences of it (from first to last). But then I have a loop after the Range.Find search which selects the last occurrence of which the cell from B1 matches the occurrence in column A (and of which B1 matches the value in Cell A1). Did you see the loop?

Finished:
    If Left(rowMatches, 1) = "," Then rowMatches = Right(rowMatches, Len(rowMatches) - 1)
 
    Dim s() As String
    s = Split(rowMatches, ",")
 
    Dim i As Integer
    For i = UBound(s) To 0 Step -1
        If .Cells(s(i), "B").Value = .Range("A1").Value Then
            .Range("A" & s(i) & ":" & "G" & s(i)).Select
            ActiveWindow.Zoom = True
            Exit Sub
        End If
    Next i
End With

If it does maybe @cmowla can then adjust the find direction to work from the bottom up to find the Last occurence.

There is no need for me to adjust the code. It works as intended. (It's correct.)

As far as I know, Range.Find doesn't search in the backwards direction. So I had to find the last cell first and then start from the beginning. I looped through all occurrences with Range.Find and stored them in a string with a comma delimiter. Then I converted that string into an array of type string. Then (because the array's arguments row numbers in ascending order), I structured my for-loop to decrement from the last argument of the array until the condition was met.

 

[cmowla]

And I tested it on his actual sheet. There is no number formatting issues. It works on his sheet as it did on my hand-made test sheet.