Using FIND function to return last value in VBA

Question

I am trying to write a function which searches through a column and return the cell which contains the last match.

For example, if I have cell A5 as my active cell

A1 -> Text
A2 -> wefwqef
A3 -> Text
A4 -> eorbebr

I would want to search through the array for 'Text' and for it to return the cell A3 as that is the last match.

But if I have cell A7 as my active cell

A1 -> Text
A2 -> wefwqef
A3 -> Text
A4 -> eorbebr
A5 -> fhyeher
A6 -> Text

Then it would return A6 as my result.

I have been playing with the FIND function to try and get it to work, but I keep getting errors (Application or object based error).

My plan is to execute this code as part of a larger loop and in each case, it returns the value closest to the active cell. (I hope that makes sense)

This is the code I have so far:

    Range("A19:I19").Select
    'ActiveCell.FormulaR1C1 = Sheets("Sheet1").Cells(9, 5).Value & ":5 CTs,
     O/C " & _
    'Sheets("Sheet1").Cells(9, 8).Value * 100 & "% @ " & _
    'Sheets("Sheet1").Cells(9, 9).Value & " TM"


    Dim Rng As Range

      With Sheets("Sheet1").Range(Cells(9, 3), Cells(Line, 3))

        Set Rng = .Find(What:="Primary", _
          After:=.Sheets("Sheet1").Cells(Line, 3), _
          LookIn:=xlValues, _
          LookAt:=xlPart, _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlPrevious, _
          MatchCase:=False)

I also imagine that when I can get this to work, I would include an DO or IF statement which would say, once value has been stored in Rng then cellx = rng.

Please help! What is wrong with this code??

Apologies if I have not followed the guidelines correctly, this has been given to me with a very short deadline! I appreciate any help given! :) — Cheves, Aug 13 '15 at 10:20

luke_t · Answer 1 · 2015-08-13T11:00:37.847

You could use an array to hold the values, and then iterate through the array in reverse, until you find the value you're after.

Sub arrayFind()
    Dim currRow As Long
    Dim wb As Workbook, ws As Worksheet, rng as Range
    Dim arrValues() As Variant
    Dim i As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    currRow = ActiveCell.Row
    arrValues = Range("A1:A" & currRow)

    For i = UBound(arrValues, 1) To LBound(arrValues, 1) Step -1
        If arrValues(i, 1) = "Text" Then
            set rng = Range(Cells(i, 1).Address)
            Exit For
        End If
    Next i
End Sub

The above creates an array from the Activecell, to A1. It then searches column A (dimension 1 of the array) for the criteria supplied (in this scenario Text) from the last item of the array, to the first. This enables it to find the matching criteria nearest to the Activecell.

Using FIND function to return last value in VBA

1 Answers1