# 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

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 A1Cells(2, 1) refers to A2Cells(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 ```

#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.

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.

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
 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 A1Cells(2, 1) refers to A2Cells(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 ```

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,Silviamessage 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.

Report •