Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
Reso

Search Sheets and Create Summary Table

Question

Hi Caleeco, 

 

Having a problem with a dashboard i'm trying to set up. Basically I have 3 sheets in my workbook

Sheet 1 - Dashboard

Sheet 2 - SAP Data

Sheet 3 - User Input

 

On the dashboard sheet I have a part number in cell A4, and what I want to do is find it on Sheet 1 and 2. Then on the dashboard I want to have a table (header row is cell D3), which will display the sheet name it appears in and where on the sheet it is. 

 

I tried doing some VLOOKUPS, but didnt have much luck. Is there a VBA way to do this?

 

 

Share this post


Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hi Reso, 

 

Thanks for the additional information. Apologies for the delay in getting back to you, upgrading websites can be very time consuming :P

 

Give this code a spin and let me know if it works on your end:

Code:

Sub FindPart()
'Source: www.ExcelWTF.com
'Purpose: To locate part number and list found location

 

'----Variables
Dim PartNo As String, Location As String
Dim ws As Worksheet, wsDash As Worksheet, wsSAP As Worksheet, wsUser As Worksheet
Dim c As Range
Dim lr As Long
Dim Found As Boolean

 

'----Code
Set wsDash = Sheets("Dashboard")
Set wsSAP = Sheets("SAP Data")
Set wsUser = Sheets("User Input")

'Set the PartNo to search for
PartNo = wsDash.Range("A4")

 

'Search for the Part Number, setting a dynamic range in column C of SAP DATA
lr = wsSAP.Range("C" & Rows.Count).End(xlUp).Row
Set c = wsSAP.Range("C2:C" & lr).Find(PartNo, , xlValues, xlWhole, xlByRows, , True)
If Not c Is Nothing Then
    Location = wsSAP.Name & " - " & c.Address(0, 0)
    Found = True
End If

 

'Search for the Part Number, setting a dynamic range in column C of User Input
lr = wsUser.Range("C" & Rows.Count).End(xlUp).Row
Set c = wsUser.Range("C2:C" & lr).Find(PartNo, , xlValues, xlWhole, xlByRows, , True)
If Not c Is Nothing Then
    If Found = True Then
        MsgBox "Part Number " & PartNo & " was found on both sheets. Exiting macro...", vbCritical, "Error"
    Else
        Location = wsUser.Name & " - " & c.Address(0, 0)
    End If
End If

 

'Print location to Dashboard sheet
wsDash.Range("D4").Value = Location

End Sub

Thanks

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Hi Reso, 

 

Thanks for the question. There are a number of ways to achieve what you have specified. However, to determine how best to help could you please answer a few follow up questions:

  1. Are the sheets names as you have specified, "Dashboard", "SAP Data" and "User Input"?
  2. Can the part number appear on both sheets? If so, do you want to list both or display an error?
  3. Can there be multiple entries of the part number in each sheet? If so, do you want to find the first one only? or all?
  4. What RANGE on each sheet needs to be searched for the part number? i.e. Column A or Cells A1:A100 etc

Let me know

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hello Caleeco,

 

Thank you for the reply! Ok I have listed my answers to your queries:

  1. Are the sheets names as you have specified, "Dashboard", "SAP Data" and "User Input"? Yes
  2. Can the part number appear on both sheets? If so, do you want to list both or display an error? It shouldn't, but an error would be good.
  3. Can there be multiple entries of the part number in each sheet? If so, do you want to find the first one only? or all? Yes, but I'm only interested in the first one found (top to bottom, as they are sorted by deliverable date)
  4. What RANGE on each sheet needs to be searched for the part number? i.e. Column A or Cells A1:A100 etc Cells C2:C? on both sheets (last row can change)

Message me if you need any more info, thanks again ^_^

Share this post


Link to post
Share on other sites
  • 0

Hello Caleeco,

 

That's ok, I've been a bit with some other projects too.

 

Thanks for the VBA code, I will test it a bit later today and let you know if I have any problems! :ph34r:

 

Share this post


Link to post
Share on other sites
  • 0

Hey Caleeco, 

 

Just got back home and spun the code through. Everything seems to work fine. One thing I noticed, when the part number is not found on either sheet... I don't get any error message? Is it possble to get a pop-up thats says "Part Number not found on either dataset"?

 

Thanks for your help!

Share this post


Link to post
Share on other sites
  • 0

Hi Reso, 

 

Sure thing, that's just a small edit. Add the following snippet before the last line (where the location is printed to the dashboard)

 

Code:

If Found = False Then
    MsgBox "Part Number " & PartNo & " not found in either dataset", vbCritical, "Error"
    Exit Sub
End If

 

Hope that helps

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Hey Caleeco!

 

Thanks for the added error message, everything is up and running now. This will be a huge timesaver! Doing this manually everytime was getting tedious xD

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×