Looking to find cell value among specific sheets in workbook

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Hi,

Thanks for looking at my question. I am working in Excel 2016.

I want to look up a specific cell value from among a range of worksheets (in the same workbook) and have it return the worksheet name the value is found on.

The sample page from the workbook is in the link below, i tried to insert the picture but was not able to get it to work.


https://imgur.com/Ga7oYlN


The values to be looked up are on sheet: Main (pictured), in cell D2 (Column Name "Item"). I want to return the looked up values to the same sheet but in cell F2 (Column Name "Site"). Then I would fill down to populate the remaining cells in column F.

The sheets in the workbook to be queried are Front, Back, Off-Set, and Turn-Over. The values on the list only occur once across all 4 worksheets. Please let me know if you need any clarification. I really appreciate any help.

Thanks,

John



 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,734
Office Version
365
Platform
Windows
Try something like this:

Code:
arr = Array("Front", "Back", "Off-Set", "Turn-Over")

With Sheets("Main")
    lr = .Range("D" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        For Each c In .Range("D2:D" & lr)
            For i = LBound(arr) To UBound(arr)
                Set myVal = Sheets(arr(i)).Cells.Find(c.Value, , , xlWhole)
                If Not myVal Is Nothing Then
                    c.Offset(0, 2) = myVal.Parent.Name
                    Exit For
                End If
            Next
        Next
    End If
End With
 

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Hi Steve,

I appreciate the response and code. I used to be more savvy with excel but haven't really used it much in years. How do I get this to work? I assume I pop this in a module but not sure how to call/execute it. Sorry for being dense, appreciate any guidance.

Regards,

John
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,734
Office Version
365
Platform
Windows
Go to the workbook in question. Press ALT and F11. In the white space paste this:

Code:
Sub nomas()

Dim arr, lr As Long, c As Range, i As Long

arr = Array("Front", "Back", "Off-Set", "Turn-Over")

With Sheets("Main")
    lr = .Range("D" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        For Each c In .Range("D2:D" & lr)
            For i = LBound(arr) To UBound(arr)
                Set myVal = Sheets(arr(i)).Cells.Find(c.Value, , , xlWhole)
                If Not myVal Is Nothing Then
                    c.Offset(0, 2) = myVal.Parent.Name
                    Exit For
                End If
            Next
        Next
    End If
End With

End Sub
Close the VBA window. Press view then macros. Double click the 'nomas' macro. You will have to save the workbook as a macro enabled (.xlsm) file.
 
Last edited:

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Steve the fish, you are awesome!

This works like a charm! Thank you so very much. Its greatly appreciated!
 

Forum statistics

Threads
1,078,520
Messages
5,340,922
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top