Checking array for existing value

[Asator]

Trying to add only unique values to an array...

This worked:

               On Error Resume Next
                matcherr = wsf.Match(zsd17.Cells(iInv, 8).Value, invNums, 0)
                If Err.Number = 0 Then
                    invFound = True
                Else
                    invFound = False
                End If
                On Error GoTo 0

This did not:

                'check currently evalued line against array of unique invoices
                'For ckInvs = LBound(invNums, 1) To UBound(invNums, 1)
                '    If Val(invNums(ckInv)) = Val(zsd17.Cells(iInv, 8).Value) Then
                '        invFound = True
                '        Exit For
                '    End If
                'Next ckInvs

The question is, why? It seemed to add every value it found. I tried with and without the Val() operator. The data its reading from is stored as text, though its all numbers.

Any ideas? Obviously its working now, I'm just trying to figure out where I went wrong on the 2nd block of code.

 

[Marcelo Branco]

Asator,

Invoice numbers (invNums) are text or numbers?

M.

 

[Asator]

They are all numbers, but they are stored as text in the file I'm reading from.

 

[Marcelo Branco]

Maybe the text-format is the culprit (not sure)

Try in the second code
If UCase(invNums(ckInv)) = UCase(zsd17.Cells(iInv, 8).Value) Then

M.

 

[Asator]

There's no letters, just numbers...

 

[Marcelo Branco]

Never mind...they are numbers, so my code makes non sense

M.

 

[Marcelo Branco]

Did you try the ugly and old debug-method?

Msgbox invNums(ckInv)
Msgbox zsd17.Cells(iInv, 8).Value

Just in case...

M.

 

[Rasm]

If you use

Option explicit

you will never have that problem again - seems you missed an S - I think

If Val(invNums(ckInvs))

 

[Asator]

Epic facepalm.

 

[Marcelo Branco]

If you use

Option explicit

you will never have that problem again - seems you missed an S - I think

If Val(invNums(ckInvs))

Good eyes!!!

I agree 1000% with Option Explicit

M.