# Thread: Macro To Loop Through Worksheets To Match Value And Return All Results Thanks:  2 Post #5340939 (1)Post #5341106 (1) Likes:  2 Post #5341106 (1)Post #5340939 (1)

1. ## Re: Macro To Loop Through Worksheets To Match Value And Return All Results

here is the workbook i'm using to test the code

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)`  Reply With Quote

2. ## 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  Reply With Quote

3. ## Re: Macro To Loop Through Worksheets To Match Value And Return All Results Originally Posted by rameezl17 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?  Reply With Quote

4. ## 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 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
6 Project 3 Project Support2
7 Project 5 Project Support
Report  Reply With Quote

5. ## Re: Macro To Loop Through Worksheets To Match Value And Return All Results

This is the workbook Ive been working with  Reply With Quote

6. ## 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 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  Reply With Quote

7. ## 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  Reply With Quote

8. ## Re: Macro To Loop Through Worksheets To Match Value And Return All Results

Scratch that it works!!!!!!!!!!!!!!!!!!!!  Reply With Quote

## User Tag List

excel, index, macro, match, vba 