# vba - How to find the last value with specific conditions?

Sheet 1 column A has the following values (it has around 3000 records. I’ve given the below sample values). I need to find the last value of a specific text.

``RVT-01RVT-02RVT-03RVT-04RVT-05RVT-06RHT-01RHT-02RHT-03RHT-04RHT-05ROI-01ROI-02ROI-03SWO-01SWO-02SWO-03SOR-01SOR-02SOR-03SOR-04SOR-05SOR-06SOR-07``

### Using VBA code

If enter short tex in `sheet1.cells(2,2) = SWO` , I need the last value in `sheet1.cells(2,4)=SWO-03`

If I enter `sheet1.cells(2,2) = RHT` , I need the last value in `sheet1.cells(2,4)=RHT-05`

If I enter `sheet1.cells(2,2) = RVT` , I need the last value in `sheet1.cells(2,4)=RVT-06`

If I enter `sheet1.cells(2,2) = SOR` , I need the last value in `sheet1.cells(2,4)=SOR-07`

What would be the VBA code for the above process?

As Skip Intro suggested, there is no need for VBA: in Column B, put a formula like this:

1. `=IF(IF(LEFT(A1,3)=LEFT(A2,3),1,0)=0,RIGHT(TRIM(A:A),2),"")` (to get the just the max number):

or

1. `=IF(IF(LEFT(A1,3)=LEFT(A2,3),1,0)=0,A:A,"")` (to get the complete contents of the cell)

Both will show you the highest values. Then you could AutoFilter that column, hiding the blanks and voila :)

Or

``````=IF(IF(LEFT(\$A1,3)=LEFT(\$A2,3),1,0)=0,NA(),"")
``````

will enable you to use SpecialCells in VBA to get a range that you can interrogate for the maximum values in each group, as below:

``````Sub test()

Dim rng As Range
Dim cell

Range("B1:B" & Range("A65536").End(xlUp).Row).Formula = "=IF(IF(LEFT(\$A1,3)=LEFT(\$A2,3),1,0)=0,NA(),"""")"
Set rng = Range(Range("B1:B" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -1).Address)

For Each cell In rng
Debug.Print cell.Address & " =" & cell.Value
MsgBox cell.Address & " =" & cell.Value
Next
End Sub
``````

For more information on the SpecialCells magic tricks, see How to delete multiple rows without a loop in Excel VBA.