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]

Its an addition to this code so now I can add one more criteria to lookup, but it will be a different range

 

[Fluff]

Ok, what's the question?

 

[rameezl17]

now i need to search the project hours worked of the name in Cell A2 of each of the projects listed in Column A5:A based on the date in Cell B2


the name range in the lookup tabs is in column F20:F24
The hours range in the lookup tabs is in Range H20:DJ24

 

[Fluff]

How about

Sub Find_Phase()
   Dim Cl As Range, Fnd As Range, Fnd2 As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Report")
   For Each Cl In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
      With Sheets(Mid(Cl.Value, InStr(1, Cl.Value, " ") + 1))
         Set Fnd = .Rows(17).Find(Ws.Range("B2").Value, , xlValues, xlWhole, , , , , False)
         If Fnd Is Nothing Then Exit For
         Cl.Offset(, 2).Value = Fnd.Offset(1)
         Set Fnd2 = .Range("F20:F24").Find(Ws.Range("A2").Value, , , xlWhole, , , False, , False)
         If Not Fnd2 Is Nothing Then Cl.Offset(, 3).Value = Intersect(Fnd2.EntireRow, Fnd.EntireColumn).Value
      End With
   Next Cl
End Sub

 

[rameezl17]

110% you are a MVP

Thank you so much for your help Fluff!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[rameezl17]

Hey Fluff theres an issue when I create a new project which creates a new Tab... it gives me a subscript out of range on this line

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

 

[Fluff]

What is the sheet name of the new project & what is the project name?

 

[rameezl17]

Well the way the file works is that each project is given a ID # and that is what the tab is called... the list starts with ID 1 and for everytime that a project is created the tab will be +1 of the previous tab. So Currently I have "1","2","3","4", and the list will continue growing once more projects are added

 

[Fluff]

What is the project name that gave the error?