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

 

[wndncg]

Wait, you want to have the last date possible in column A that matches E1 whose value in the same row in Column B matches the value in A1?

yesyes

 

[cmowla]

yesyes

Okay, nevermind my previous comment then. I'll get to it!

 

[cmowla]

Sorry for the delay. I was trying to use Application.WorksheetFunction.Match instead of Range.Find (just for fun), but it failed on me. Will get to that on my own time! (Run the top/small sub.)

Sub Test__Find_Latest_Date()
Call Find_Latest_Date("TEMPLATE", Sheets("TEMPLATE").Range("E1").Value, "A:A")
End Sub
Sub Find_Latest_Date(sheetName As String, search As String, searchRangeAddress As String)

With Sheets(sheetName)
    Dim FoundCell As Range, myRange As Range, LastCell As Range
    Set myRange = .Range(searchRangeAddress)

    'By default, the search find feature will start searching from the beginning once it's at the end.
    'So to guarantee that the search starts at the beginning, make the start cell to search the last cell in the search range.
    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)

    'See if anything was found
    If Not FoundCell Is Nothing Then
        Dim rowMatches As String, firstOccurrenceAddress As String
        firstOccurrenceAddress = FoundCell.Address

        'Loop until cycled through all unique finds
        Do Until FoundCell Is Nothing
            rowMatches = rowMatches & "," & FoundCell.Row
            Set FoundCell = myRange.Find(What:=search, after:=FoundCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
            If FoundCell.Address = firstOccurrenceAddress Then GoTo Finished
        Loop
    Else
        GoTo Nothing_Found
    End If
 
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

Nothing_Found:
MsgBox "Not found", vbInformation, "Search Complete"

End Sub

 

[wndncg]

Sorry for the delay. I was trying to use Application.WorksheetFunction.Match instead of Range.Find (just for fun), but it failed on me. Will get to that on my own time! (Run the top/small sub.)

Sub Test__Find_Latest_Date()
Call Find_Latest_Date("TEMPLATE", Sheets("TEMPLATE").Range("E1").Value, "A:A")
End Sub
Sub Find_Latest_Date(sheetName As String, search As String, searchRangeAddress As String)

With Sheets(sheetName)
    Dim FoundCell As Range, myRange As Range, LastCell As Range
    Set myRange = .Range(searchRangeAddress)

    'By default, the search find feature will start searching from the beginning once it's at the end.
    'So to guarantee that the search starts at the beginning, make the start cell to search the last cell in the search range.
    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)

    'See if anything was found
    If Not FoundCell Is Nothing Then
        Dim rowMatches As String, firstOccurrenceAddress As String
        firstOccurrenceAddress = FoundCell.Address

        'Loop until cycled through all unique finds
        Do Until FoundCell Is Nothing
            rowMatches = rowMatches & "," & FoundCell.Row
            Set FoundCell = myRange.Find(What:=search, after:=FoundCell, SearchDirection:=xlNext, MatchCase:=True, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
            If FoundCell.Address = firstOccurrenceAddress Then GoTo Finished
        Loop
    Else
        GoTo Nothing_Found
    End If
 
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

Nothing_Found:
MsgBox "Not found", vbInformation, "Search Complete"

End Sub

no sorry sir, i will test it right now.

 

[wndncg]

where do i put the A1 and E1?

 

[cmowla]

where do i put the A1 and E1?

It's already put in there.

The E1 is here:

Call Find_Latest_Date("TEMPLATE", Sheets("TEMPLATE").Range("E1").Value, "A:A")

The A1 is here:

        If .Cells(s(i), "B").Value = .Range("A1").Value Then

 

[wndncg]

It's already put in there.

The E1 is here:

Call Find_Latest_Date("TEMPLATE", Sheets("TEMPLATE").Range("E1").Value, "A:A")

The A1 is here:

        If .Cells(s(i), "B").Value = .Range("A1").Value Then

what date format should user input in E1??

 

[cmowla]

what date format should user input in E1??

I am guessing the same format as those in Column A. (That works on my "practice" sheet.)

 

[wndncg]

I am guessing the same format as those in Column A. (That works on my "practice" sheet.)

not working will send you the pass code

Filebin | y67o34lrxdlf882o

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net

 

[wndncg]

cant message you

not working will send you the pass code

Filebin | y67o34lrxdlf882o

Convenient file sharing. Think of it as Pastebin for files. Registration is not required. Large files are supported.

filebin.net