问题描述:

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-01

RVT-02

RVT-03

RVT-04

RVT-05

RVT-06

RHT-01

RHT-02

RHT-03

RHT-04

RHT-05

ROI-01

ROI-02

ROI-03

SWO-01

SWO-02

SWO-03

SOR-01

SOR-02

SOR-03

SOR-04

SOR-05

SOR-06

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

相关阅读:
Top