Macro To Loop Through Worksheets To Match Value And Return All Results

rameezl17

Board Regular
Joined
Mar 6, 2018
Messages
105
Hi Everyone,

I am trying to build a macro that takes the value in Sheet name - "Report" Cell "A2" (this is going to be a employees name), looks through all the sheets in my workbook and returns each project name that their name is in one of the sheets going down from Cell "A5". So If their name appears 3 times within all the tabs in my workbook I want it to return the project name which will be in Cell E20 in all the other tabs.

So if I were to do this within excel and NOT VBA it would look like this -
=INDEX('1'!E20:E24,MATCH('Report'!A2,'1'!$G$20:$G$24))

The '1' is the name of the tab that it has the info i need to match to. (All the worksheets are numbered 1,2,3,4,5, and will continue growing as more projects come in)
I need the above formula to loop through all workbooks and once it finds a match to post the result in cell A5 and keep going down from there for each match that it finds

Thank you for your help!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Keep getting error on line

If Sheets("Report").Ramge("A2").Value = ary1(j,6) Then

what does the error say?
its probably because it says "ramge" and not "range"
 
Last edited:
Upvote 0
check the spelling, if that doesnt fix it hover over ary1 while its debugging and tell me if thats the subscript out of range

Not the spelling, when I hover over it says "ary1(j,6) = [Subscript out of range]<subscript out="" of="" range=""><subscript out="" of="" range="">"</subscript></subscript>
 
Last edited:
Upvote 0
Not the spelling, when I hover over it says "ary1(j,6) = <subscript out="" of="" range="">"


click view > watch window
while debugging drag and drop ary1(j,6) into the watches window
what does it show for value?
same thing for "j"

</subscript>
 
Upvote 0
Im getting an Invalid Watch Expression error message

something was changed in the code then?
are you combining codes?
if you want to add this sub in between some code use
Call timesTHREE

i don't see any reason the code would fail if sheets 7+ have data with no blank rows/columns from A1 to F24
 
Upvote 0
I copied the code you gave me as a separate sub. The most recent code you gave me is only for finding out project roles right
 
Upvote 0
I copied the code you gave me as a separate sub. The most recent code you gave me is only for finding out project roles right

it loops through sheets 7 - end of workbook
it assigns the current looped sheet as an array (this is probably where you're getting the error as i can't see your data. Try running this sub and pasting the range that is selected for '1')
Code:
Sub sSelect()
ActiveSheet.Range("A1").CurrentRegion.select
End Sub

it then loops through the rows of the sheet and for every row it checks column F for the name from A2

the error is prior to the rest of the code but it does this:

if the name was found in F21-F24 it will return a Project Support to column B of Report
if the name was found in F20 it will return project lead to column B of report
both if statements also include grabbing E20 and printing it to Column A of report
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top