Search across all worksheets and return the last cell in that column

[lukephillips]

Hi, I need a code that, for instance will search across multiple sheets, each called a different letter of the alphabet, for a surname, and then return the value of the cell at the bottom of its column.
I have tried looking at searching over multiple sheets, and returning the last value of a specific column, but cant get the two to work together.
Any help would be appreciated.

 

[lukephillips]

also, if possible, i would like to be able to search upto 7 names at a time. but i know this might be a bit much to ask... I was thinking i could select the names from a listbox, that i can easily populate manually, rather than using another search code.

Thanks for any help/

 

[My Aswer Is This]

If I understand correctly you want to search every cell in every sheet in your workbook for a certain name in a listbox. And then if the value is found what do we do next.

And if we are looking for "George" is that all that will be in the cell or may the cell have "Good Morning George"

You said:

for a surname, and then return the value of the cell at the bottom of its column.

So does that mean if we find "George" in Range ("G14") we return the value in the last row of Column "G"
And return it where?
It would be easier to put the search values in a range then in a Listbox. Either way we need the name of the listbox and in what sheet is the listbox or the range where the search values are.

 

[Peter_SSs]

Welcome to the MrExcel board!

Some additional questions to those already asked.

search across multiple sheets, each called a different letter of the alphabet

1. Is there a sheet for every letter of the alphabet (A-Z)? If not, can you give more details?

2. Are all these 'alphabet-named' sheets together in a single group in the workbook tabs?

3. What other sheets are there in the workbook and where are those sheets placed in relation to the 'alphabet-named' sheets?

4. Are there any restrictions on where we look for the particular surname in each sheet? For example, can it only be in column G? Can it only be in columns C:F? Can it be anywhere at all? etc

 

[My Aswer Is This]

I will leave this question and I'm sure Peter will have a answer for you.

 

[lukephillips]

Hi. Thanks for the replys.
The workbook is currently 27 worksheets. The first is called Instructions. The other 26 are called "A", "B" etc.... the names are currently going across in row "B" of every worksheet.

The names are actually (SURNAME, Firstname). Eg. PHILLIPS, Luke.
Basically I want to be able to input, preferably 7 names at once, and return the last date that's underneath each of those names. (This way we can easily see which person was the furtherest away).

I don't mind if it's a thing where you type the name in to look them up, or it's just a list that you highlight the seven names to compare.

Thank you again for the help. I hope I'm not too confusing.
-Luke-

 

[Peter_SSs]

the names are currently going across in row "B" of every worksheet.

That doesn't make sense to me.
Rows do normally go across the sheet but are labelled 1, 2, 3, etc, not "B"
Columns are labelled A, B, C etc but run down the sheet, not across.
Please clarify.


What about question 4 of my previous post?

.. and return the last date that's underneath each of those names.

Is it possible that a name could be found in more than one worksheet and/or in more than one column of a particular worksheet? If either is possible, how to proceed?

 

[My Aswer Is This]

So we are going to search all sheets from Sheet(2) to the last sheet in the workbook
And we will be searching only row (2) for the names in the Listbox Named Listbox1 located on Sheet(1)

But you never answered a very important question.

You said:
"and return the last date that's underneath each of those names."

But you did not say Return the date "Where"

 

[lukephillips]

Sorry. Yes I got a little confused there. The surnames are in row "2".

The names will only appear once in the entire workbook. There are no duplicate names. And as per previous line, they are all in row "2" of each sheet.

My answer is this, yes. You are correct. As for the return the date where... let's say sheet 1, column "B" rows 4-10.
I hope that makes sense.

 

[Peter_SSs]

Assuming the 7 names of interest are in A4:A10 of the left hand worksheet, then try this in a copy of your worksheet.

Sub GetLastValuePerName()
  Dim c As Range, rFound As Range
  Dim i As Long
  
  For Each c In Sheets(1).Range("A4:A10")
    For i = 2 To Sheets.Count
      Set rFound = Sheets(i).Rows(2).Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
      If Not rFound Is Nothing Then
        c.Offset(, 1).Value = Sheets(i).Cells(Rows.Count, rFound.Column).End(xlUp).Value
        Exit For
      End If
    Next i
  Next c
End Sub