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

 

[Fluff]

How about

Sheets("Report").Cells(5, 3) = found.Offset(1).Value

 

[rameezl17]

Fluff,

thank you!!!!!!!

can I ask you for a question to make this macro more dynamic?

 

[Fluff]

You can, that's what the site is for

 

[rameezl17]

Hi Fluff,

Please see the link of my workbook and refer to Module 4, i have included a description. Thank you so much for your help!

​​​​​​​https://drive.google.com/open?id=0Bx4MCAsH5k_UM1pRbHQ4VDEtUnBIRk40bk9nNlZETnhVVTBr

 

[Fluff]

Can you please post you question to the thread, that way everyone see what you want.
Thanks.

 

[rameezl17]

I now need the macro to loop through all the tabs except for "Report" and when it matches the projects name found in cell A5 Down (this list will depend on how many projects are assigned to the project owner) (Cell A2 in report)
to a tab then the macro needs to look through row 17 in all those tabs to match the date and then return the Project phase which is the (Found.Offset(1).Value)

 

[Fluff]

Ok, how about

Sub Find_Phase()
   Dim Cl As Range, Fnd As Range
   Dim Sht As String
   
   With Sheets("Report")
      For Each Cl In .Range("A5", .Range("A" & Rows.Count).End(xlUp))
         Set Fnd = Sheets(Mid(Cl.Value, InStr(1, Cl.Value, " ") + 1)).Rows(17).Find(.Range("B2").Value, , xlValues, xlWhole, , , , , False)
         If Not Fnd Is Nothing Then Cl.Offset(, 2).Value = Fnd.Offset(1)
      Next Cl
   End With
End Sub

 

[rameezl17]

Yesssssssssssssssssssssssss works perfectly!!!

How about one more?? :DD

 

[Fluff]

Is it directly related to this code?
If so yes, otherwise you need to start a new thread