Macro To Lookup Value In Array And Return Value From Second Row

[rameezl17]

Hi Everyone,

I currently have this macro

Sub Find_Phase()
Dim found As Range
Set found = Sheets("1").Rows("17:18").Find(what:=Sheets("Report").Cells(2, 2).Value, LookIn:=xlValues, lookat:=xlWhole)
If found Is Nothing Then
Else
Sheets("Report").Cells(5, 3) = found.Row
End If
End Sub

So far it returns the row number to my "Report" Sheet, however I need the macro to return the value in row 18 that is underneath the row 17 value...

for an example, if the value is found in sheets("1") row 17 column G i need the value in my "Report" sheet to be from sheets("1") row 18 column G.

Im not sure if this macro is the right direction but i am stuck

 

[rameezl17]

Actually.... I guess I mustve messed something up when I combined it in the same Module as a different code....I apologize!
I just made one Sub that Calls all the codes I need done.

The issue is no longer there my mistake!

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[rameezl17]

Okay I take it back.....is there a reason to why if the project name doesn't = Project Name "#"
that it throws the macro off?

For example I add a new project named "Pacman"

when I try to then run the report it gives me the error again of the Subscript is out of Range on this line:

With Sheets(Mid(Cl.Value, InStr(1, Cl.Value, " ") + 1))

Sorry for being a bother

 

[rameezl17]

When I try to debug that line I see that it captures that correct value for C1.... however it says:

InStr(1, C1.Value, " ") = 0

 

[Fluff]

The code is taking everything after the space in "Project 1" and using that as the sheet name.

 

[rameezl17]

Is there anyway to adjust it so we can just make it match whatever is in that Cell of A5 and below?

 

[Fluff]

If the sheet name is the same as A5 down, then yes.

 

[rameezl17]

Okay I see what you mean.. hmmm
the thing is that each of the sheet names are only going to be number 1,2,3,4,5,6,7,8 and will continue growing.
the project name will not be the same as the sheet names, the sheet names is just the ID

 

[Fluff]

If the sheet name & project name have no bearing to each other, how do you now which is which?

 

[rameezl17]

So I have a macro currently that will lookup a individual name and loop through all the project information sheets to know which projects an individual name has been working on, and same for their project role.

I need the macro to loop through all the project sheets and look for the values and once it finds it then to provide me the info i need:


this is the code that i use to fill in the Project associated with a specific individual, and their role within that project:

Sub Generate_Report()



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(2, 1)
            If k <> 1 Then
            Sheets("Report").Cells(5 + x, 2).Value = "PE Support" & k
            x = x + 1
            Else
            Sheets("Report").Cells(5 + x, 2).Value = "PE Support1"
            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(2, 1)
    Sheets("Report").Cells(5 + x, 2).Value = "PE Lead"
    x = x + 1
    




Else
End If
End If




Next j
Next i


End Sub