Search worksheet and return all values in column directly below reference cell

[mattm1981]

Hi, is is possible for me to write a formula for excel to search a worksheet for a specific value(s) and return all values in the column directly below where it finds the result? The use case is to extract data from property tenancy schedules. The schedules are all different formats so I would like Excel to search all columns and if it finds 'Lease expiry date' in say G16, then it will return all values from G17 down to say G2000 and paste to another worksheet.
Thanks for the help

 

[Anbuselvam]

Sub ExtractData()

    Dim searchValue As String
    Dim searchRange As Range
    Dim resultRange As Range
    Dim foundCell As Range
    Dim lastRow As Long
    Dim outputRow As Long
   
    'Set the search value to look for
    searchValue = "Lease expiry date"
   
    'Set the search range to look for the search value
    Set searchRange = ActiveSheet.UsedRange
   
    'Find the first occurrence of the search value in the search range
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
   
    'If the search value is found
    If Not foundCell Is Nothing Then
        'Set the result range to be the column directly below where the search value is found
        lastRow = ActiveSheet.Cells(Rows.Count, foundCell.Column).End(xlUp).Row
        Set resultRange = Range(foundCell.Offset(1, 0), Cells(lastRow, foundCell.Column))
       
        'Copy the result range to a new worksheet
        outputRow = 1
        For Each cell In resultRange
            Sheets("Output").Cells(outputRow, 1) = cell.Value
            outputRow = outputRow + 1
        Next cell
    End If
   
End Sub

 

[mattm1981]

Thanks that worked perfectly!!
Forgive my ignorance but how would I then expand this to:

1) Add alternatives to "Lease expiry date" - for example "Lease end date"? There are probably 20 different ways this same information is headed on the dataset so I'd like to cover all the possible alternatives
2) Perform the same exercise but for a different value i.e "Tenant Name"? I'm basically looking to pull all the info on the sample tabs across to the Output.

I've tried to do this by copying in the above but have rarely used VBA so struggling to get it to work.

Thanks again

 

[Anbuselvam]

Post a sample file link with expected results to understand better to provide an exact solution.

 

[mattm1981]

Sorry the mini sheet option won't work on my work computer. Sample tenancy schedule below. At the moment what you provided works great but I receive different forms of data so where D3 says 'Lease end date' rather than 'Lease expiry date', can I build in flexibility for this to be picked up?

I also then want to do the exact same thing for Tenant Name, Area etc.

Thanks

 

[Peter_SSs]

@Anbuselvam
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Sorry the mini sheet option won't work on my work computer.

In what way will it not work? What goes wrong? Could this be the issue?
If that is not the problem, then please describe exactly what goes wrong at at what point in the instructions it happens.

 

[mattm1981]

@Anbuselvam
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.


In what way will it not work? What goes wrong? Could this be the issue?
If that is not the problem, then please describe exactly what goes wrong at at what point in the instructions it happens.

It looks like I need Administrator access to add the file to a trusted location. I've raised a ticket but who knows how long it will take them to resolve. Thanks for tip anyway

 

[Anbuselvam]

searchValue = "Lease expiry date" Replace this line with any like Tenant Name, Area etc.,

 

[Anbuselvam]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Okay,

 

[mattm1981]

searchValue = "Lease expiry date" Replace this line with any like Tenant Name, Area etc.,

Can it search for more than one value? I/e if it can't find 'Lease expiry date', it will search for 'Lease end date' instead?