Solved Look for a text value across multiple worksheets

September 13, 2015 at 23:03:29
Specs: Windows XP
Hello, I'm struggling with this problem. I need a formula that reference a value starting in $H4, looks for it across multiple worksheets and returns the worksheet(s) name or, even better, also the cell(s). There are 5 worksheets in total, the value will be text, not numbers. Thank you, Silvia

See More: Look for a text value across multiple worksheets

Report •


✔ Best Answer
September 15, 2015 at 20:49:42
In the previous macro I used the Cells method to reference the cells where the Sheet Names and Cell Address would be placed. The Cells method has 2 arguments:

Cell(row_index, column_index)

Cells(1, 1) refers to A1
Cells(2, 1) refers to A2
Cells(1, 2) refers to B1 as does Cells(1, "B")

I held the row_index argument constant and used a variable to determine the column_index argument. Whatever the current value of dstCol is, that is the column that will contain the next Sheet Name or Cell Address but always in Row 4.

Cells(4, dstCol)

In this version, I'll hold the column_index arguments constant ("T" and "U") and use a variable for the row_index argument.

Cells(dstRw, "T")
Cells(dstRw, "U")

You could also use this, since Column 20 is Column T and 21 is U:

Cells(dstRw, 20)
Cells(dstRw, 21)

Try this version:

Sub FindValues()
Dim dstRw As Long
Dim c As Range
'Initalize Destination Row variable
   dstRw = 10
'Loop through Sheet 2 through last sheet
     For shtNum = 2 To Sheets.Count
'Search entire sheet(s) for H4 value
       With Sheets(shtNum).Cells
        Set c = .Find(Sheets(1).Range("$H$4"), lookat:=xlPart)
'If value is found, place Sheet Name and Cell Address in the 
'next Row in Columns T & U
           If Not c Is Nothing Then
              firstAddress = c.Address
                Do
                   dstRw = dstRw + 1
                   Sheets(1).Cells(dstRw, "T") = Sheets(shtNum).Name
                   Sheets(1).Cells(dstRw, "U") = c.Address
'Continue searching individaul sheet(s) until all values are found
                 Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
           End If
       End With
     Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 14, 2015 at 05:16:44
You will need a macro to accomplish your goal. I can offer one if using macros is OK. Please let me know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 14, 2015 at 22:25:48
Thank you for your answer. I suposse it is ok to do a macro, but my only idea was to write one applying the commands "select all sheets" "Edit - Find" "Find all".

It will accomplish the search but will not write anywhere the name of the worksheet and the cell where the data was found.

Or could it?

Thank you! Silvia


Report •

#3
September 15, 2015 at 08:44:33
I don't know how much you know about writing or editing macros, so I'll offer some code just to get you started.

The code below loops through the sheets in the workbook looking for the value in Sheet1!H4 and then returning the sheet name(s) and cell address(es) each time a matching value is found.

I don't know the layout of your workbook or where you want the results placed, so this is just a generic example of how it could be done.

With a value in Sheet1!H4, this code will search all the other sheets and return the Sheet Name for the first match in I4 and the cell address for the first match in J4. It will then return the Sheet Name for the second match in K4 and the cell address for the second match in L4, etc.

Sub FindValues()
Dim dstCol As Long
Dim c As Range
'Initalize Destination Column variable
   dstCol = 8
'Loop through Sheet 2 through last sheet
     For shtNum = 2 To Sheets.Count
'Search entire sheet(s) for H4 value
       With Sheets(shtNum).Cells
        Set c = .Find(Sheets(1).Range("$H$4"), lookat:=xlPart)
'If value is found, place Sheet Name and Cell Address in "next" columns
           If Not c Is Nothing Then
              firstAddress = c.Address
                Do
                   dstCol = dstCol + 1
                   Sheets(1).Cells(4, dstCol) = Sheets(shtNum).Name
                   Sheets(1).Cells(4, dstCol + 1) = c.Address
                   dstCol = dstCol + 1
'Continue searching individaul sheet(s) until all values are found
                 Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
           End If
       End With
     Next
End Sub

If you would like to try to "reverse engineer" the code to see how it works, perhaps this debugging tutorial will help:

http://www.computing.net/howtos/sho...

Let us know if we can be of any other assistance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 15, 2015 at 17:52:36
Hello again and thank you for the answer. I know a little about macros but not this much.

I used your code and it does work. It is exactly what I need. However, when the the amount of hits exceed the amount of columns available (I didn't think about that possibility), it gets stucked and comes back with an error message.

I assume this could be solved by placing the returns in rows instead of columns. Is that possible? For example: first result in T10 - U10, second result in T11 - U11, third in T12 - U12 and so on.

I tried to do it myself but failed (miserably I might add). Thank you for your help. Regards, Silvia


Report •

#5
September 15, 2015 at 20:49:42
✔ Best Answer
In the previous macro I used the Cells method to reference the cells where the Sheet Names and Cell Address would be placed. The Cells method has 2 arguments:

Cell(row_index, column_index)

Cells(1, 1) refers to A1
Cells(2, 1) refers to A2
Cells(1, 2) refers to B1 as does Cells(1, "B")

I held the row_index argument constant and used a variable to determine the column_index argument. Whatever the current value of dstCol is, that is the column that will contain the next Sheet Name or Cell Address but always in Row 4.

Cells(4, dstCol)

In this version, I'll hold the column_index arguments constant ("T" and "U") and use a variable for the row_index argument.

Cells(dstRw, "T")
Cells(dstRw, "U")

You could also use this, since Column 20 is Column T and 21 is U:

Cells(dstRw, 20)
Cells(dstRw, 21)

Try this version:

Sub FindValues()
Dim dstRw As Long
Dim c As Range
'Initalize Destination Row variable
   dstRw = 10
'Loop through Sheet 2 through last sheet
     For shtNum = 2 To Sheets.Count
'Search entire sheet(s) for H4 value
       With Sheets(shtNum).Cells
        Set c = .Find(Sheets(1).Range("$H$4"), lookat:=xlPart)
'If value is found, place Sheet Name and Cell Address in the 
'next Row in Columns T & U
           If Not c Is Nothing Then
              firstAddress = c.Address
                Do
                   dstRw = dstRw + 1
                   Sheets(1).Cells(dstRw, "T") = Sheets(shtNum).Name
                   Sheets(1).Cells(dstRw, "U") = c.Address
'Continue searching individaul sheet(s) until all values are found
                 Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
           End If
       End With
     Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 15, 2015 at 22:03:14
It works perfectly. I can't thank you enough.

But thank you, thank you, thank you!

Regards,
Silvia

message edited by SilviaB


Report •

#7
September 16, 2015 at 04:13:32
Now that you have 2 versions that do what you want in 2 different ways, you should spend some time using the debugging techniques described in the tutorial, such as Watches and Single Stepping.

The purpose of this would be two-fold. Not only will you get a better understanding of how the macros work, but you'll familiarize yourself with the debugging tools so that the next you attempt to write or modify a macro, you can do your own troubleshooting.

If you have any further questions, don't hesitate to ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question