Page 5 of 5 FirstFirst ... 345
Results 41 to 48 of 48

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

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

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

    here is the workbook i'm using to test the code
    https://drive.google.com/file/d/1fw_...ew?usp=sharing

    tell me whats different here?
    edit:

    Code:
    Sheets("Report").Cells(5it the + x, 1).Value = ary1(j, 5)
    was an oopsie change that back to

    Code:
    Sheets("Report").Cells(5 + x, 1).Value = ary1(j, 5)
    Last edited by BlakeSkate; Sep 12th, 2019 at 04:00 PM.
    -------------------------------------------------------------------------------
    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

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

    so when i use your code in my file it gives me the correct roles (Project Support 1 and then Project Support 4, but it does it in column A & B which then removes the project name in Project A. Currently trying to test out to code to see if I can fix it

  3. #43
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    468
    Post Thanks / Like
    Mentioned
    6 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 when i use your code in my file it gives me the correct roles
    i thought you were getting a subscript out of range error?
    what fixed it?

    Code:
     For k = 1 To 4
            If ary1(j, 6) = ary1(20 + k, 6) Then
                Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
                If k <> 1 Then
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support" & k
                x = x + 1
                Else
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support"
                x = x + 1
                End If
            
            End If
        Next k

    the 5 + x, 1 means A5
    the 5 + x, 2 means B5


    Code:
    If ary1(j, 6) = ary1(20, 6) Then
        Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
        Sheets("Report").Cells(5 + x, 2).Value = "Project Lead"
        x = x + 1
    Else
    End If
    same for this


    so the "Project lead" and "Project Support"s should not be overwriting the project name found in E20
    if you have any errors its because something was changed as i've attached a link to the workbook i'm using in post #41 and it works as intended?
    Last edited by BlakeSkate; Sep 12th, 2019 at 04:40 PM.
    -------------------------------------------------------------------------------
    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

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

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

    in my supplied workbook running this code

    Code:
    Sub timesTHREE()
    Dim x As Long, i As Long, j As Long, k As Long, p As Long
    Dim ary1 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 rows of the array
    For j = LBound(ary1) To UBound(ary1)
        
    'find  matches between A2 and array
    If Sheets("Report").Range("A2").Value = ary1(j, 6) Then
    
    'loop to find project supports
        For k = 1 To 4
            If ary1(j, 6) = ary1(20 + k, 6) Then
                Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
                If k <> 1 Then
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support" & k
                x = x + 1
                Else
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support"
                x = x + 1
                End If
            
            End If
        Next k
              
    'if to find project leads
    If ary1(j, 6) = ary1(20, 6) Then
        Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
        Sheets("Report").Cells(5 + x, 2).Value = "Project Lead"
        x = x + 1
    Else
    End If
    End If
            
    Next j
    Next i
    
    End Sub
    i get this result in sheets "Report"

    A B
    1
    2 Boofles
    3
    4
    5 Project 1 Project Lead
    6 Project 3 Project Support2
    7 Project 5 Project Support
    Report
    -------------------------------------------------------------------------------
    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. #45
    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

    https://drive.google.com/open?id=0Bx...tHV0ZUdXZoNzNZ

    This is the workbook Ive been working with

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

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

    You have the project name in D20 not E20

    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
    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 rows of the array
    For j = LBound(ary1) To UBound(ary1)
        
    'find  matches between A2 and array
    If Sheets("Report").Range("A2").Value = ary1(j, 6) Then
    
    'loop to find project supports
        For k = 1 To 4
            If ary1(j, 6) = ary1(20 + k, 6) Then
                Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 4)
                If k <> 1 Then
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support" & k
                x = x + 1
                Else
                Sheets("Report").Cells(5 + x, 2).Value = "Project Support"
                x = x + 1
                End If
            
            End If
        Next k
              
    'if to find project leads
    If ary1(j, 6) = ary1(20, 6) Then
        Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 4)
        Sheets("Report").Cells(5 + x, 2).Value = "Project Lead"
        x = x + 1
    Else
    End If
    End If
            
    Next j
    Next i
    
    End Sub
    alternatively change:
    ary1(20, 4) to ary1(2, 1)
    since both are the project name
    Last edited by BlakeSkate; Sep 12th, 2019 at 05:22 PM.
    -------------------------------------------------------------------------------
    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. #47
    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

    So that gave me the correct format but... my name is on 2 of the project tabs and the report is only showing Project 1

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

    Scratch that it works!!!!!!!!!!!!!!!!!!!!

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
  •