Error 1004 on Worksheet.Match

[kodiac9]

I get error 1004 on this snippet of code. Plz help!

   Dim Cert2008, Cert2009, Cert2010, Cert2011, Cert2012, Cert2013, Cert2014, Cert2015, Cert2016, Cert2017, Cert2018, Cert2019, Cert2020, Cert2021, Cert2022, Cert2023, Cert2024, Cert2025, Cert2026, Cert2027, Cert2028, Cert2029, Cert2030 As Integer
   Dim certused(), Check As Variant
   Dim certcounter As Integer
certcounter=0
' ======= SKIPPED WORKING PART=========
 
 
   ' === Get data =======
    ActiveCell.Offset(0, 3).Range("A1").Select
    Check = ActiveCell.Value
    '== Check if exist =======
        lngLoc = Application.WorksheetFunction.Match(Check, certused(certcounter), 0)
    If Not IsEmpty(lngLoc) Then
    Else
          '===== Add to string ===
            ReDim Preserve certused(certcounter + 1)
         certused(certcounter) = ActiveCell.Value
         '=== Prepare for next ======
         certcounter = certcounter + 1
        Cert2008 = Cert2008 + 1
    End If
    ActiveCell.Offset(0, -3).Range("A1").Select

 

[CharlesChuckieCharles]

to start

Dim Cert2008, Cert2009, Cert2010, Cert2011, Cert2012, Cert2013, Cert2014, Cert2015, Cert2016, Cert2017, Cert2018, Cert2019, Cert2020, Cert2021, Cert2022, Cert2023, Cert2024, Cert2025, Cert2026, Cert2027, Cert2028, Cert2029, Cert2030 As Integer

means on Cert2030 is an integer all the other certs are variants

 

[kodiac9]

oh.. lol ^^ thanks

ok, so i changed it to this

   Dim Cert2008 As Integer
   Dim certused(), Check As Variant
   Dim certcounter As Integer
certcounter=0
' ======= SKIPPED WORKING PART=========
 
   ' === Get data =======
    ActiveCell.Offset(0, 3).Range("A1").Select
    Check = ActiveCell.Value
    '== Check if exist =======
        lngLoc = Application.Match(Check, certused(certcounter), 0)
    If Not IsEmpty(lngLoc) Then
          '===== Add to string ===
            ReDim Preserve certused(certcounter)
         certused(certcounter) = ActiveCell.Value
         '=== Prepare for next ======
         certcounter = certcounter + 1
        Cert2008 = Cert2008 + 1
        MsgBox (Cert2008)
    End If
    ActiveCell.Offset(0, -3).Range("A1").Select

I goes through the first data, but then it crashes 1004 again at the first duplicate

Oh, what i am trying to do is count items only once in a loop (this is snippet from loop) so i put them in array if it isnt there and when its there it should skip to else.

 

[CharlesChuckieCharles]

we probably need to see the "working part"

 

[RoryA]

If Not IsEmpty(lngLoc) Then

should be:

If Not IsError(lngLoc) Then

assuming lngloc is a Variant in spite of its name.

 

[kodiac9]

Yes it's a variant

i did it like you said, but now it just skips the whole if part
its suppose to check, if exist then skip, else add to array and add +1 to cert2008
wut it is doing now is Error on start and never adds it

i tried putting it in else, and it still gives 1004 on first duplicate

  ' === Get data =======
    ActiveCell.Offset(0, 3).Range("A1").Select
    Check = ActiveCell.Value
    '== Check if exist =======
        lngLoc = Application.Match(Check, certused(certcounter), 0)
    If Not IsError(lngLoc) Then
    Else
          '===== Add to string ===
            ReDim Preserve certused(certcounter)
         certused(certcounter) = ActiveCell.Value
         '=== Prepare for next ======
         certcounter = certcounter + 1
        Cert2008 = Cert2008 + 1
        MsgBox (Cert2008)
    End If
    ActiveCell.Offset(0, -3).Range("A1").Select

 

[RoryA]

There should not be an Else in there - it defeats the purpose of the test. If IsError is true, then the value was not found and you want to skip. If it's always skipping then none of your values are being found - most likely cause of that is you are looking up numbers against numeric values stored as text, or vice versa.

 

[baitmaster]

do you mean worksheetfunction.match?

 

[RoryA]

It's the same thing except the WorksheetFunction version raises a runtime error if the match is not found, whereas Application.Match returns an actual error value that you can test with IsError.

 

[baitmaster]

what, and ignore it with ON ERROR?! awesome, I wondered why that particular function always caused problems... thanks Rory