I'm trying to write a function that will act as a vba way to run the vlookup formula over multiple sheets. I'm not married to this method, but need the same result. I'm getting a #VALUE! now, so I'm not so sure that my function is even written correctly - I'm not too familiar. Can someone please look it over and tweak as necessary?
Function Find1(sRng, sLocation As Integer) As Double Application.Volatile True For i = 1 To 9 Sheets("Data " & i).Select Range("A2").Select Cells.Find(What:=sRng, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate Find1 = ActiveCell.Offset(0, sLocation) Next i End Function
In B7 I have "1" I want to search column B on sheets Data 1 through Data 9. I want to then return the value in the column directly to the right of the first "1" found.
I call my function using:
I would mostly like to use a function to take advantage of not being forced to "run a macro" or anything like that.
Thanks for all the help - once again!