mikechambers
Active Member
- Joined
- Apr 27, 2006
- Messages
- 397
I have the following function which works fine if it is on the sheet it is trying to search on.
Function MYFUN(RowTerm As String, ColumnTerm As String)
Range("A1").Select
RowNum = Cells.Find(What:=RowTerm, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range("A1").Select
ColNum = Cells.Find(What:=ColumnTerm, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
MYFUN = Cells(RowNum, ColNum).Value
End Function
BUT, I want to use this on another sheet in the same workbook. How can I have the function on one sheet, but do everything else based on the other sheet? In other words, I want to find the RowTerm and ColumnTerm on the other sheet and give me the intersecting value.
Function MYFUN(RowTerm As String, ColumnTerm As String)
Range("A1").Select
RowNum = Cells.Find(What:=RowTerm, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range("A1").Select
ColNum = Cells.Find(What:=ColumnTerm, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
MYFUN = Cells(RowNum, ColNum).Value
End Function
BUT, I want to use this on another sheet in the same workbook. How can I have the function on one sheet, but do everything else based on the other sheet? In other words, I want to find the RowTerm and ColumnTerm on the other sheet and give me the intersecting value.