asked on

Excel VBA Find First Find last

I need to construct a vba function that when given a column in a table (Listobject)
it will find the first and last occurence of a given string . It will than copy the data between
these occurences and the rows to another sheet, please help.

ASKER CERTIFIED SOLUTION

ExcelGuide

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thrawn3000

ASKER

Hi PSchotec
That looks fine but I need it to find the last cell tht contains the same search criteria a s the first, not simly the last cell in the range . I have put together the below code that does this, thanks very much for your help on this.

I pass tp the below function two parameters the search item I'm looking for in this case a date as
dtmFindValue and the Range in which to look for as rnglookup which is a column in a table.
The function then finds the address of the last and firts cell in the given range that match the search item
and copies this range to a newsheet including additional columns in the range.

It works weell because it makes use of the Find function which allows it to look for the search string from the bottom of the range and than from the top of the range hence finding the last and first occurence given that the range is sorted.

Public Function fncTargetRange(dtmFindvalue As Date, rnglookup As Range) As Range

Dim rngLast As Range
Dim rngFirst As Range

Set rngLast = rnglookup.Find(What:=dtmFindvalue, _
After:=rnglookup.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

Set rngFirst = rnglookup.Find(What:=dtmFindvalue, _
After:=rnglookup.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFirst Is Nothing Then

Set fncTargetRange = wksData.Range(rngFirst, "AU" & rngLast.Row)

'LoaD Calc table with Data
fncTargetRange.Copy Destination:=grntblCalc

Else

Exit Function

End If

End Function

Thrawn3000

ASKER

Hi PSchotec
That looks fine but I need it to find the last cell tht contains the same search criteria a s the first, not simly the last cell in the range . I have put together the below code that does this, thanks very much for your help on this.

I pass tp the below function two parameters the search item I'm looking for in this case a date as
dtmFindValue and the Range in which to look for as rnglookup which is a column in a table.
The function then finds the address of the last and firts cell in the given range that match the search item
and copies this range to a newsheet including additional columns in the range.

Public Function fncTargetRange(dtmFindvalue As Date, rnglookup As Range) As Range

Dim rngLast As Range
Dim rngFirst As Range

Set rngLast = rnglookup.Find(What:=dtmFindvalue, _
After:=rnglookup.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

Set rngFirst = rnglookup.Find(What:=dtmFindvalue, _
After:=rnglookup.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFirst Is Nothing Then

Set fncTargetRange = wksData.Range(rngFirst, "AU" & rngLast.Row)

'LoaD Calc table with Data
fncTargetRange.Copy Destination:=grntblCalc

Else

Exit Function

End If

End Function