User defined function does not maintain input ranges from proper worksheet

[kdwoell]

New to VBA (and generally a lousy programmer).
I wrote this function based on a YouTube vid.
It works and I use this UDF in multiple worksheets in an Excel file. Problem is if the function updates in one worksheet it affects the function in another worksheet.
Excel built-in functions preserve their original argument cell references - but obviously I'm missing something.
Any help?

[VBA]
Function ReverseLookup(MatrixValue As Range, LookupTable As Range)
HRow = LookupTable.Rows(1).Row - 1
HCol = LookupTable.Columns(1).Column - 1

ReverseLookup = ""

For Each cell In LookupTable
If cell.Value = MatrixValue.Value Then
ReverseLookup = ReverseLookup & Cells(HRow, cell.Column).Value & "," & Cells(cell.Row, HCol).Value & ", " & cell.Value
End If
Next cell

End Function

[/VBA]

[Bob Phillips]

Not sure what it does, but is this any better

[vba]

Function ReverseLookup(MatrixValue As Range, LookupTable As Range)
Dim HRow As Long
Dim HCol As Long
Dim cell As Range

HRow = LookupTable.Rows(1).Row - 1
HCol = LookupTable.Columns(1).Column - 1

ReverseLookup = ""

For Each cell In LookupTable
If cell.Value = MatrixValue.Value Then
ReverseLookup = ReverseLookup & cell.Parent.Cells(HRow, cell.Column).Value _
& "," & Cells(cell.Row, HCol).Value & ", " & cell.Value
End If
Next cell
End Function
[/vba]

[kdwoell]

So this is what I'm trying to do. I got tables with row and col headers. I gotta do reverse lookups based on a value in the table. In addittion to headers I need the value in the table associated with the col and row headers. Then I gotta parse all that crap and sort it, etc. So shamelessly stole some code off the web.
Here is the table I'm testing. On a different worksheet is a similar table (but with completely different data).
apples pears
dog 2 3
cat 5 1

Here is the output from your version of the function. Works great....
apples,dog, 2 pears,dog, 3
apples,cat, 5 pears,cat, 1

But still same problem. The other output in the other worksheet is getting overwritten with the values from the first worksheet. I should not have to pre-append the worksheet name (i.e. "firstWorksheet!blah blah") to the function input range. One certainly does not worry about that when using Excel built-in functions.

Wierd. This is why I hate programming.

[p45cal]

I think xld might have missed one bit; try changing:
[vba]ReverseLookup = ReverseLookup & cell.Parent.Cells(HRow, cell.Column).Value & "," & Cells(cell.Row, HCol).Value & ", " & cell.Value [/vba] to
[vba]ReverseLookup = ReverseLookup & cell.Parent.Cells(HRow, cell.Column).Value & "," & cell.Parent.Cells(cell.Row, HCol).Value & ", " & cell.Value[/vba]

[kdwoell]

Yup that fixed it. Thanks a ton "p45cal"...and thanks to "xld" too.

So in review...this snippet ensures the function only accepts arguments from the same worksheet in which it is invoked...
[VBA]
cell.Parent.Cells(HRow, cell.Column).Value
[/VBA]

Whereas this I guess is global...(and hence bad)
[VBA]
Cells(HRow, cell.Column).Value
[/VBA]

I was actually thinking this was the issue and reading up on "ActiveSheet" or something like that.
So when the so called veteran programmers were demonstrating this code did they leave this detail out for expediency or....what?

thanks again!

[Bob Phillips]

@p45Cal Thanks , I need to replace these goggles!

@kdwoell it is not that it is global when it is not qualified, but more that it will assume the activesheet of the ativeworkbook, whatever that may be.

Don't be too hard on the veteran programmers (leave that to us ), it is a common mistake, we all have made it at some time. I personally don't like loops in UDFs, but I can't get the results you show no matter what I put in MatrixValue (BTW, that is a garbage name, as it seems it is nothing to do with a matrix), I just get ,,apples, ,,dog, or ,,2, so I cannot suggesy a better alternative.