Inventory System

[jdautel]

Ok, I'm working on a little project for inventory management.. I'm doing this just for practice in excel and vba. I'm having a weird issue with my code:

Option Explicit
Function missing(x As Range, a As Range) As String
Dim xx As Double
xx = x.Value
Dim cell As Range, z As Range, zoro As String
    For Each cell In a
        If cell.Value = xx Then
            Set z = Range(cell.Address)
            z.Select
            zoro = ActiveCell.EntireRow.Cells(1).Value
        End If
    Next
MsgBox z
MsgBox zoro
missing = zoro
End Function


Sub missingtest()
Dim balling As String
balling = missing(Range("H2"), Range("I6:I25"))
MsgBox balling


End Sub

So the problem I'm having is that if I run the sub procedure it will work and display the string I am looking for; however if I run the function within the spreadsheet using the exact same ranges used in the sub, it returns nothing at all. I've been messing with it for over an hour fixing little things and can't figure it out. Any help appreciated

[offthelip]

Try this

[VBA]Function missing(x As Range, a As Range) As String
xx = x.Value
For Each cell In a
If cell.Value = xx Then
i = cell.Row
zoro = Cells(i, 1).Value
End If
Next
MsgBox z
MsgBox zoro
missing = zoro
End Function


[/VBA]

[jdautel]

Worked like a charm. Any idea why the other one was working in the sub procedure but refused to work in the actual worksheet?

Thanks for your help! I'll have to remember to use Cells(i,1) when I want to use a variable to locate a cell.

[offthelip]

Yes it is because you were selecting a different cell in a worksheet function which changes the activecell. You should never do that in a worksheet function, it is likely to cause problems. Try to avoid select at all times if possible.

[jdautel]

Ohh, ok got it. That makes sense. Thanks