PDA

View Full Version : Inventory System



jdautel
09-08-2016, 12:55 PM
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
09-08-2016, 01:18 PM
Try this

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

jdautel
09-08-2016, 01:43 PM
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
09-08-2016, 02:10 PM
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
09-09-2016, 08:18 AM
Ohh, ok got it. That makes sense. Thanks