Need to search specific sheets 1

[SBuzzT]

I have the code below that searches the entire workbook for a value entered in C3 of sheet LOOKUP and returns the results specified in row 6 of the same sheet. What I need it to do, is to search for a value only on specific sheets in the workbook (and preferably in the column I specify) and then return the results. I was hoping to be able to manage the sheets it searches through by entering the sheet names in a column of a worksheet that would only have that information (kind of a control panel). That way, people can add or remove sheets from the search without going into the VBA.

So far, no luck. Any ideas are appreciated greatly.

Sub ID_Lookup()

Dim rng As Range, Cel, ms As Worksheet, ws As Worksheet, k, NR&

Set ms = Sheets("LOOKUP")

Application.ScreenUpdating = 0

Sheets("LOOKUP").Range("C6:E7").ClearContents

     With ms
       Cel = .Range("C3")
     End With
     
     NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1  '+3?
        
     For Each ws In ThisWorkbook.Worksheets
     
         If ws.Name <> ms.Name Then
        
            With ws.UsedRange
            
                    If Len(Cel) Then
                          
                            Set rng = .Find(Cel, LookIn:=xlValues, LookAt:=xlPart)
                                                                                                                                                              
                               If Not rng Is Nothing Then
                                    k = k & "," & ws.Name
                                    rng.Offset(, -13).Copy
                                    ms.Range("C" & NR).PasteSpecial xlValues
                                    rng.Copy
                                    ms.Range("D" & NR).PasteSpecial xlValues
                                    ms.Range("E" & NR) = Mid(k, 2)
                                    Range("C3").Select
                                    Range("C3").ClearContents
                               End If
                               
                             With Range("D6")
                                .Value = Replace(.Value, "&", ", ")
                            End With
                    End If
     
            End With
  End If
 Next
 
   If Range("C6").Value = "" Then MsgBox ("Number " & Cel & " not found.")
   If Range("C6").Value = "" Then Sheets("LOOKUP").Range("C3").ClearContents
 
Application.CutCopyMode = 0
Set ms = Nothing
Set rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

hi,

What is the structure of your workbook/worksheets?

Do your sheets have tables with HEADINGS?

Are you looking for data in a specific HEADING on your specified sheets?

So far, no luck.

Exactly what does that mean? The procedure did not run to completion? The procedure completed but returned an incorrect result? What was the lookup value? What sheets had that value in what column/row?

There's LOTS more information that you need to provide. EACH and EVERY question above begs an response.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Why -13???

rng.Offset(, -13).Copy

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

Sorry for the lack of detail. And let me add, I'm no great VBA expert - I work with what I've got. Let me try to elaborate...

The search works fine right now. It searches every sheet in the workbook for the search criteria (the id is always in column O right now).

The -13 gets me the value from column B of the row where the search values was found.

For example, if I search A1234, it would find that value on each sheet it was present, get the values from column B(-13 from column O), column O (there could be more than 1 value, hence the use of xlPart instead of xlWhole) and also the sheet name, and put them into C3, D3 and E3 respectively.

SAMPLE RESULT
[THE NAME IN COLUMN B] [A1234, 56543, QR123] [SHEET NAME1, SHEET NAME 2]

In its current state, some sheets have heading, some do not. (This is just the way each person has been maintaining them - inconsistent, I know.) The id number is always in column O and the -13 gets me the value from column B.

 

[SkipVought]

Okay, have you STEPED thru your procedure to observe WHAT VALUE is going into WHAT CELL as you loop thru your code.

But FIRST, change this code

rng.Offset(, -13).Copy

to this

.Cells(rng.Row, "B").Copy

because, "The -13 gets me the value from column B of the row where the search values was found."

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

Thanks very much for that. But I just don't know how to tell it (using this code) to search on Sheet1, Sheet2 and Sheet3 instead of the whole workbook (there could be many sheets). That's my main problem.

 

[SkipVought]

What I'm saying is, you have some OTHER problems to solve BEFORE you start looking at only certain sheets.

1) have you corrected your COPY statement?

2) have you stepped thru your code to carefully observe what values are being put in what cells in your lookup sheet?

Then you can answer the question, is the code doing what it should at this juncture before you go and add more complexity.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

I have corrected the copy statement (thanks again).

When the search criteria is located on a sheet, it is returning:
[ul]
[li] The value from Column B of that sheet into C6 of LOOKUP.[/li]
[li] It is returning the value of Column O (the whole cell, which includes the search criteria) into D6 of sheet LOOKUP.[/li]
[li] And finally, it returns the name of each sheet the search criteria was located on (comma separated) into E6 of sheet LOOKUP.[/li]
[/ul]
This is working perfectly.

Now, I'm just trying to figure out how to have it search only the sheets I tell it to, rather than searching the whole workbook as it is now.

Sorry if I'm misunderstanding anything you're asking me. I appreciate your patience.

 

[SBuzzT]

I should mention that the values returned in Column O and Column B are be the same on each worksheet. Oly the worksheet names differ.

 

[SBuzzT]

Sorry for the fat fingers... I should mention that the values returned from Column O and from Column B are the same on each worksheet. Only the worksheet names differ.

 

[SkipVought]

When the search criteria is located on a sheet, it is returning:

•The value from Column B of that sheet into C6 of LOOKUP.
•It is returning the value of Column O (the whole cell, which includes the search criteria) into D6 of sheet LOOKUP.
•And finally, it returns the name of each sheet the search criteria was located on (comma separated) into E6 of sheet LOOKUP.

In my attempt to set up a workbook to run your code, I did NOT get the results that you describe above. I have the search string in THREE sheets in DIFFERENT columns, the value in column B in each of these sheets is DIFFERENT, and here is a summary of my results.
[tt]
The value from column B get OVERWRITTEN with the data from each sheet that has the search value on it
The COPIED value that gets pasted into column D on the lookup sheet is ALSO OVERWRITTEN.
NOTHING is written into D6! The application starts putting data in to ROW 4, then incriments each time the procedure is run.
The sheets containing the lookup value are in column E
[/tt]
So that's what I see running your code!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, your code does NOT look for the lookup value in column O!

Rather it looks for the lookup value in the USED RANGE: anywhere on the sheet!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

You're right: It does look anywhere on the sheet, but the value will be in column O. Originally, there were some sheets it appeared elsewhere, but as I need to be more and more specific, I got everyone to agree to keep the numbers in column O (at least I won that battle).

Not sure why, but the copy statement you provided is not working now (after I closed and reopened workbook), so I went back to what I had before. But, here is what my results look like:

And here's my code again (I've been playing with it and want to make sure my screen shots reflect my current code)

Sub ID_Lookup()

Dim rng As Range, Cel, ms As Worksheet, ws As Worksheet, k, NR&

Set ms = Sheets("LOOKUP")

Application.ScreenUpdating = 0

Sheets("LOOKUP").Range("C6:E7").ClearContents

     With ms
       Cel = .Range("C3")
     End With
     
     NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 3  '+3?
        
     For Each ws In ThisWorkbook.Worksheets
     

         If ws.Name <> ms.Name Then
        
            With ws.UsedRange
            
                    If Len(Cel) Then
                          
                            Set rng = .Find(Cel, LookIn:=xlValues, LookAt:=xlPart) 'xlWhole
                                                                                                                                                              
                               If Not rng Is Nothing Then
                                    k = k & "," & ws.Name
                                    rng.Offset(, -13).Copy
                                    '.Cells(rng.Row, "B").Copy
                                    ms.Range("C" & NR).PasteSpecial xlValues
                                    rng.Copy
                                    ms.Range("D" & NR).PasteSpecial xlValues
                                    ms.Range("E" & NR) = Mid(k, 2)
                                    Range("C3").Select
                                    Range("C3").ClearContents
                               End If
                               
                             With Range("D6")
                                .Value = Replace(.Value, "&", ", ")
                            End With
                    End If
     
            End With
  End If
 Next
 
   If Range("C6").Value = "" _
   And Range("D6").Value = "" _
   And Range("E6").Value = "" _
   Then MsgBox ("Number " & Cel & " not found.")
   If Range("C6").Value = "" Then Sheets("LOOKUP").Range("C3").ClearContents
 
Application.CutCopyMode = 0
Set ms = Nothing
Set rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Well the caveat in your example is that the column B value on each sheet is identical and the lookup result on each sheet is identical. If these values are not identical on each sheet where the lookup value is found, then only the column B value and the lookup value in the cell FROM THE LAST SHEET in which the lookup value is found, will be stored in the lookup sheet.

Okay, so I now get your results, it seems, with the caveat stated above.

So make a list WITH A HEADING on a separate sheet, of the sheet names to include. Macro record nameing the range using the HEADING (Formulas > Defined Names > Create from selection -- Create names from values in the TOP ROW)

Then in your loop insert another loop to check the ws.name against the names in the new list and if its found there, then execute the copy/paste code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SBuzzT]

I'll give that a shot. Thanks again, Skip.

 

[SkipVought]

post back with your code if you need help customizing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DaveInIowa]

Here's my 2 cents...

For each sheet you want to search, select the column containing the search values and give it a name beginning with 'Search'. Then, whenever you want to perform your search you can loop through these names to get the sheets and columns you want to search.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nm As Name
    
    For Each nm In Application.Names
        If (nm.Name Like "Search*") Then
            Dim prompt As String
            prompt = "Name: " & nm.Name & vbCrLf & "RefersToRange.Address: " & nm.RefersToRange.Address
            MsgBox prompt
        End If
    Next
    
End Sub