Using VBA or Formula to search multiple rows of data and return specified column values (must return all results and not first match)

[SeanKC1989]

Afternoon All,

I am having some trouble trying to find a formula or script that is able to search for a specified value in around 40 columns (some will be blank) and then if a match is found then to return the data from either one specific column or a group of columns.
This step must then be repeated and all results for the match given (not just the first or last).
There are 500 or so rows so the script or formula must be able to handle multiple rows and columns simultaneously.

For example I have a value on sheet 3, I would like to search all rows in sheet 1 for this value and each time there is a match return the values from column 46 in sheet 1. Is this possible?

Please help

 

[mumps]

It is certainly possible but it would help if we had more information. In which cell in Sheet3 is the value to match? Do you want to search the entire Sheet1 or only a particular column in Sheet1? Where on Sheet3 do you want to return the values from column 46?

 

[SeanKC1989]

Hi Mumps and thanks for the reply,

The value is located in column A and is a list of values but I’m happy to run it with a single changeable value in sheet 3 or even in the same sheet if required.

I would like to search a specific range which, without looking at the sheet, is A4:AH448 (approximately) in sheet 1.

Oh and I really don’t mind where the data is returned, just that it is returned somewhere in sheet 3 other than column A

Thanks again for your help

Sean

 

[mumps]

Try this macro. It will search for all values in column A of Sheet3 and return the value in column 46 of Sheet1 to the same row of the value in column A.

Sub ReturnVal()
    Application.ScreenUpdating = False
    Dim LastRow1 As Long, LastRow3 As Long, rng As Range, sAddr As String, Val As Range, lCol As Long, desWS As Worksheet, srcWS As Worksheet
    Set desWS = Sheets("Sheet3")
    Set scrWS = Sheets("Sheet1")
    LastRow1 = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRow3 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In desWS.Range("A2:A" & LastRow3)
        Set Val = srcWS.Range("A4:AH" & LastRow1).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not Val Is Nothing Then
            sAddr = Val.Address
            Do
                lCol = desWS.Cells(rng.Row, desWS.Columns.Count).End(xlToLeft).Column + 1
                desWS.Cells(rng.Row, lCol) = srcWS.Cells(Val.Row, 46)
                Set Val = srcWS.Range("A4:AH" & LastRow1).FindNext(Val)
            Loop While Val.Address <> sAddr
            sAddr = ""
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

The macro assumes your values in column A start in row 2.

 

[SeanKC1989]

Thank you very much Mumps,

I will give it a try shortly and let you know if it works really appreciate the help

 

[SeanKC1989]

Hey mumps,

Getting an error that states object variable or with block variable not set ??? Any ideas

 

[SeanKC1989]

This section of the code (in red below)

Sub ReturnVal()
Application.ScreenUpdating = False
Dim LastRow1 As Long, LastRow3 As Long, rng As Range, sAddr As String, Val As Range, lCol As Long, desWS As Worksheet, srcWS As Worksheet
Set desWS = Sheets("Sheet9")
Set scrWS = Sheets("Sheet1")
LastRow1 = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRow3 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In desWS.Range("A2:A" & LastRow3)
Set Val = srcWS.Range("A4:AH" & LastRow1).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
If Not Val Is Nothing Then
sAddr = Val.Address
Do
lCol = desWS.Cells(rng.Row, desWS.Columns.Count).End(xlToLeft).Column + 1
desWS.Cells(rng.Row, lCol) = srcWS.Cells(Val.Row, 46)
Set Val = srcWS.Range("A4:AH" & LastRow1).FindNext(Val)
Loop While Val.Address <> sAddr
sAddr = ""
End If
Next rng
Application.ScreenUpdating = True
End Sub

 

[SeanKC1989]

OK I found the error
it was in the set row. It was set as SCR instead of SRC. Having changed that, it all seems ok but there are no values returned at all??
real strange

 

[SeanKC1989]

OK Now I have it returning data, but it isn't the correct data?
Originally i had given the incorrect column number to return

 

[mumps]

Change the 46 in the code to the column number of the column you want to return.