[lucasarts]

Hi all,

This may be more complex than I think but I have searched hundreds of forum posts all over the place and while I've come close to finding a solution to this; nothing has quite described a way to do this...

Basically, I have a roster for staff (attached is a simple sample I've thrown together to show you what's on the rows / headers & sample contents...obviously the actual spreadsheet is much bigger and month-to-view on each sheet).

Let's assume the following:
- sheet 1 and sheet 2 have staff rosters on as per the example spreadsheet
- sheet 3 is where I want to display the search results
- the same name may/will be present on different dates and different shifts
- this is for me and not an 'end user' so it doesn't have to be in any way flash in its working or pretty!!!

I would like a search entry box on sheet 3 (let's say in cell A1).

I need the following results returned for EACH occurrence of the searched name (this is where it gets beyond me). These will be copied into sheet 3 let's say starting from cell A3 - I'll stick in a clearcontents on the range at the start of the sub as the results will be copied into an email and then can be cleared when I need a new search:

- Shift (always found in Column A of the sheet being searched)
- Job Number (always found in in Column B of the sheet being searched)
- Date of shift (always found in Row 1 of the sheet being searched)

So essentially it's a lookup / find. The results you get in a Ctrl+F 'find all' are basically the results I need, but just 'looking up' the row title and column header.

Is this possible / simple? Any code snippets appreciated - very happy to tinker where my abilities allow!!!

Thanks for any help in advance.

Luke

Ps. In case it helps; I need to do this to generate a summary of any one persons shifts by searching their name. I have 150+ freelancers working for me and generating a summary over a 4 month project usually takes me forever; I'm trying to automate the process once I have entered their names into the shifts I'd like them to do.

[Rollin_Again]

Can you modify your sample file to show how Sheet3 should look for one of your example names?

Regards,
Rollin

[lucasarts]

Hi Rollin_Again,

Many thanks for getting back to me.

I have amended the spreadsheet as you requested. Rather than show an example, I've described exactly how it should function in sheet3 and the layout therein.

Cheers,
Luke

[bomb #21]

I dunno if I viewed the "before" or "after" now, but ...

... if you enter "Dave" as the search term and then run the code below, it should colour all the "Dave"s on the month sheets.

That's a start, yes?

Sub test()
Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
Numsheets = Sheets.Count
SearchName = Sheets("Search Results").Range("A1")
For i = 1 To Numsheets - 1
Sheets(i).Select
x = Range("A1").End(xlToRight).Column - 2
y = Range("A" & Rows.Count).End(xlUp).Row - 1
For Each Cell In Range("C2").Resize(x, y)
If Cell = SearchName Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
Next i
Sheets(StartSheet).Select
Application.ScreenUpdating = True
End Sub

[bomb #21]

Maybe this (make sure your work's "safe" first, of course):

Sub test()
Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
Sheets("Search Results").Select
Numsheets = Sheets.Count
SearchName = Range("A1")
x = Range("A" & Rows.Count).End(xlUp).Row
If x > 3 Then
Rows(4).Resize(x - 3).Delete
End If
For i = 1 To Numsheets - 1
Sheets(i).Select
x = Range("A1").End(xlToRight).Column - 2
y = Range("A" & Rows.Count).End(xlUp).Row - 1
For Each Cell In Range("C2").Resize(x, y)
If Cell = SearchName Then
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(1) = _
Cells(1, Cell.Column)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 1) = _
Cells(Cell.Row, 1)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = _
Cells(Cell.Row, 2)
End If
Next Cell
Next i
Sheets(StartSheet).Select
Application.ScreenUpdating = True
End Sub

[lucasarts]

Bomb#21....I cannot thank you enough!!

That literally does EXACTLY what I need it to do!

I'm so so grateful for your work on this; let me know if there is anything I can ever do for you!

Peace,
Luke

[lucasarts]

Okay, I've just been playing around with this in the 'actual' workbooks and having a few issues...

Main problem is that there are often blank cells which I believe this code ends if it finds, have tried amending to:

Sub test()
Application.ScreenUpdating = False
StartSheet = ActiveSheet.Name
Sheets("Search Results").Select
Numsheets = Sheets.Count
SearchName = Range("A1")
x = Range("A" & Rows.Count).End(xlUp).Rows
If x > 3 Then
Rows(4).Resize(x - 3).Delete
End If
For i = 1 To Numsheets - 1
Sheets(i).Select
x = Range("A1").End(xlToRight).Column - 2
y = Range("A" & Rows.Count).End(xlUp).Row - 1
For Each Cell In Range("C2").Resize(x, y)
If Cell = SearchName Then
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(1) = _
Cells(1, Cell.Column)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 1) = _
Cells(Cell.Row, 1)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = _
Cells(Cell.Row, 2)
End If

Cell.IsEmpty = True Then
ActiveCell = ActiveCell.Offset(1, 0)
End If

Next Cell
Next i
Sheets(StartSheet).Select
Application.ScreenUpdating = True
End Sub

But this isn't working (bit in red is addition to your code, Bomb#21).

Also, quick other question: if I was to insert an extra row at the top of the schedules (to manually insert days of the week above the 'date' fields), how could I get the loop to adjust to this? For ease, I have attached a blanked out version of my ACTUAL spreadsheet (which I should probably have done in the first place!). The code you wrote is already in place on 'ThisWorkbook'.

Thanks again for help,

Luke

[bomb #21]

1. "Ordinary" code doesn't go in the ThisWorkbook module.

2. You won't get anywhere with ActiveCell.

3. You slipped in some major "structural" changes (row 4 is now the "starter" on month sheets, Sheet3 is completely new from out of nowhere, etc.).

4. I'll think about it.

[bomb #21]

OK. As always, code needs "constants" to be able to get its bearings.

I note that in A2 of every month sheet is "Position / Shift", so we'll use that.

Enter some name in Search Results!A3, then the same name in one hatched cell on one of the month sheets. Then run the "blah" sub in the attached. You should get one msgbox telling you where you made the entry (cell address/sheet name).

If that works, come back & explain "insert an extra row at the top of the schedules (to manually insert days of the week above the 'date' fields)" for me.

Sub blah()
Sheets("Search Results").Select
SearchName = Range("A1")
x = Range("A" & Rows.Count).End(xlUp).Row
If x > 3 Then
Rows(4).Resize(x - 3).Delete
End If

For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Cells(2, 1) = "Position / Shift" Then
Sheet.Select
x = Sheet.Range("A" & Rows.Count).End(xlUp).Row
y = Sheet.Range("C1").End(xlToRight).Column
Sheet.Range("C4").Resize(x - 3, y - 2).Select
For Each Cell In Selection
If Cell = SearchName Then
MsgBox Cell.Address, , Sheet.Name
End If
Next Cell
End If
Range("A1").Select
Next Sheet

Sheets("Search Results").Select

End Sub

[lucasarts]

Hi Bomb...

Firstly, sorry - I didn't think I was making any changes that would affect the usage of the code. Regards to your numbered points:

1. Would it go in the 'Search Results' worksheet code then rather than ThisWorkbook?

2. Copy that on the ActiveCell! ;-)

3(a). I clearly misunderstood what the code you wrote was doing: I thought that adding sheets to the workbook would be fine due to the NumSheets line. It would be best if the number of months was flexible due to varying project lengths etc (if adding sheets requires changes in the code; if you could just briefly explain where this is located in the code, I'm eager to learn!).

3(b). row 3 (rather than 2) is actually the "starter" - the black 'title strip' may not exist on other projects where there is just 1 show running. Thus it's not a problem / better if the search range includes row '3'.

4. I'm sorry for complicating it! I tried to understand what the code was doing and work around it - I need to learn more!!!

Thanks again,
Luke

[bomb #21]

1. A standard module, via Insert > Module in the V(isual) B(asic) E(ditor).

3 (a) added sheets are covered (b) including row 3 requires:

Sheet.Range("C3").Resize(x - 2, y - 2).Select

instead.

[lucasarts]

Thanks, I understand.

A2 is, and will always be, constant - agreed.

I did as instructed and it did return a msgbox detailing the sheet name and cell reference as you said.

Please ignore the 'insert an extra row...' comment - you're already seeing what I meant by that (i.e. row 1 on the spreadsheet you're now looking at is days of the week from column C and what was in row 1 on my initial simple mock-up is now in row 2...pos/shift, job number, dates). Does that make sense?

Appreciate your help, once again!

Luke

[bomb #21]

OK. Can you install this mod yself or do you need me to upload it?

Sub blah2()
Sheets("Search Results").Select
SearchName = Range("A1")
x = Range("A" & Rows.Count).End(xlUp).Row
If x > 3 Then
Rows(4).Resize(x - 3).Delete
End If

For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Cells(2, 1) = "Position / Shift" Then
Sheet.Select
x = Sheet.Range("A" & Rows.Count).End(xlUp).Row
y = Sheet.Range("C1").End(xlToRight).Column
Sheet.Range("C3").Resize(x - 2, y - 2).Select
For Each Cell In Selection
If Cell = SearchName Then

Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(1) = Cells(2, Cell.Column)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 1) = Cells(Cell.Row, 1)
Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Cells(Cell.Row, 2)

End If
Next Cell
End If
Range("A1").Select
Next Sheet

Sheets("Search Results").Select

End Sub

[lucasarts]

AMAZING!! That seems to work a treat!!

This is complete yes? I can rename the module Search Results or whatever and upload it into a (backed up!) version of my final sheet?

Many thanks buddy.

Luke

[bomb #21]

You can simply copy & paste the text of the sub into (a module in) your "live" version if you prefer.

(as long as you don't end up with 2 subs with the same name ; that just causes confusion )