Page 1 of 5 123 ... LastLast
Results 1 to 10 of 48

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

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    Quote Originally Posted by rameezl17 View Post
    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.
    ...

    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
    So the activesheet which is "Report"you want to take the value of A2 and you want to loop through each sheet that is not "Report"
    If the name from Report matches anywhere on "Tab" sheets 3 times you want to return cell E20 to A5 of sheet "Report"

    am i understanding that correctly?
    Last edited by BlakeSkate; Sep 12th, 2019 at 09:51 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  3. #3
    Board Regular
    Join Date
    Mar 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    Yes, if theres only 1 match then itll return that into cell A5, if it finds another match then continue going down from A5

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    Also do sheets "1" "2" "3" etc contain any blank rows/columns?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    Board Regular
    Join Date
    Mar 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    No blank rows / columns in those sheets

  6. #6
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    cool. one more thing. the naming convention of sheets being "1" "2" "3" is not ideal and will cause problems with checking if those sheets exist. Are they able to be named "Tab1" "Tab2" "Tab3" instead?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    I kinda need them to be numbered 1,2,3,etc because its a ID name given when a new project is created

  8. #8
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    Quote Originally Posted by rameezl17 View Post
    I kinda need them to be numbered 1,2,3,etc because its a ID name given when a new project is created
    okay well when i make a loop and ask VBA if sheets(x) exists it will treat it as the first sheet rather than the sheet named "1"
    so if i'm doing an alternative approach are there are sheets besides "Report" that i need to ignore? maybe a sheet that may contain a persons name 3 times that aren't the tabs?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  9. #9
    Board Regular
    Join Date
    Mar 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    Yes could you use this function?

    wsCOUNT = Application.Sheets.Count
    For i = 7 To wsCOUNT

    since all those numbered tabs come after one of another in order, and all the other tabs that i dont want the match are before them.

    if you cant use the above macro then the tab names that need to be ignored are "Project Overview", "Validation Lists", "New Project Template", "Capacity Dashboard", "Database"
    Last edited by rameezl17; Sep 12th, 2019 at 10:25 AM.

  10. #10
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro To Loop Through Worksheets To Match Value And Return All Results

    try this

    Code:
    Sub timesTHREE()
    Dim x As Long, i As Long, j As Long, k As Long, p As Long
    Dim ary1 As Variant, key As Variant
    Dim wsCOUNT As Long
    Dim ws As Worksheet
    Dim lastROW As Long, lastCol As Long
    
    
    wsCOUNT = Application.Sheets.Count
    
    'loops through the sheets
    For i = 7 To wsCOUNT
        k = 0
    
    'gets the sheets last row and last column
    lastROW = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
    lastCol = Sheets(i).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    
    'sets the current sheet as the array given there are no blank rows/columns
    ReDim ary1(1 To lastROW, 1 To lastCol)
    ary1 = Sheets(i).Range("A1").CurrentRegion.Value2
    
    'loop through the coiumns of the array
    For x = 1 To UBound(ary1, 2)
    
    'loop through the rows of the array
    For j = LBound(ary1) To UBound(ary1)
        
        'find  matches between A2 and array then count them
        If Sheets("Report").Range("A2").Value = ary1(j, x) Then
        k = k + 1
        End If
    
    
    Next j
    Next x
       
    'if there were 3 and more paste the value to the range
    If k >= 3 Then
    Sheets("Report").Cells(5 + p, 1).Value = Sheets(i).Range("E20").Value
    p = p + 1
    End If
    
    Next i
    End Sub
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •