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

 

[RoryA]

If you use WorksheetFunction, then you need an On Error .... statement; if you use the Application. versions, you just assign the result to a variant and then use

If Iserror(var) Then

syntax

 

[kodiac9]

How do i make it add the value to the array, if it is missing then because from what i gather, wutever number the active cell has, this will make IsError always true.

   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
   Dim lngLoc As Variant
   certcounter = 0
    ReDim certused(certcounter)
         ReDim Preserve certused(certcounter)
         certused(certcounter) = 0
         '=== Prepare for next ======
         certcounter = certcounter + 1
 
   ' === 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
          '===== Add to string ===
            ReDim Preserve certused(certcounter)
         certused(certcounter) = ActiveCell.Value
         '=== Prepare for next ======
         certcounter = certcounter + 1
        Cert2008 = Cert2008 + 1
        MsgBox (certused(certcounter))
    End If
    ActiveCell.Offset(0, -3).Range("A1").Select

 

[RoryA]

Ah, I misread your code - you do actually want to process where the errors occur:

   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
   Dim lngLoc As Variant
   certcounter = 0
 
   ' === 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
          '===== Add to string ===
            ReDim Preserve certused(certcounter)
         certused(certcounter) = check
         '=== Prepare for next ======
         certcounter = certcounter + 1
        Cert2008 = Cert2008 + 1
        MsgBox (certused(certcounter))
    End If
    ActiveCell.Offset(0, -3).Range("A1").Select

 

[kodiac9]

Now with this code i get runtime error 9 : out of range on this line

       lngLoc = Application.Match(Check, certused(certcounter), 0)

At first run, not even on duplicate

 

[kodiac9]

Heres my file

http://kodiac.info/Example.xls

it has to count for whatever year the amount of different Cert nos, without counting repetitions.

Cert2008 should be 2

 

[RoryA]

   certcounter = 0
    ReDim certused(certcounter)
' more code here


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

 

[kodiac9]

http://kodiac.info/Example.xls

msg box should pop with the number 2 in it, and it did not, meaning the whole part under match is skipped.

 

[RoryA]

That appears to be because the test only gets done once, due to the flow of your logic. It is inside the Select Case statement and only gets run for 2008 as far as I can see at a quick glance. (far too many Select statements in there to really see what's going on easily)

 

[kodiac9]

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

Dunno wut i changed in there but now it works flawlessly

Thanks a lot for your help!