runtime error 5 invalid procedure call or argument

[wCJanssen]

Hi,

The following code is intended to loop through a list of dates, format them into "YYYY"-format, check if there's an identical item in the array yet, add any new items to the array and write it to a worksheet. It returns 'runtime error 5 invalid procedure call or argument', though.

Private Sub cmd_try_Click()
Dim dataRange As Range
Dim oneCell As Range
Dim newYear As String
Dim arr_years() As String
Dim i As Integer
 
Set dataRange = Range(Sheets("Overzicht_aanmeldingen").Cells(4, 10), Sheets("Overzicht_aanmeldingen").Cells(4, 10).End(xlDown))

For Each oneCell In dataRange
If Not IsError(Application.Match(oneCell.Value, arr_years, 0)) Then
i = i + 1
newYear = CStr(Year(oneCell.Value))
ReDim Preserve arr_years(1 To i)
arr_years(i) = newYear
Else: Exit For
End If
Next oneCell

Sheets("Aanmeldingen_per_maand").Range("B2", Range("B2").End(xlToRight)) = arr_years
End Sub

Could you help me get this to work, please? Thanks!

 

[JoeMo]

Assuming this line:

If Not IsError(Application.Match(oneCell.Value, arr_years, 0)) Then

causes the error, the worksheet function Match requires a range object as the second argument, not an array.

Can you elaborate on exactly what you want your code to do?

 

[Andrew Poulsom]

worksheet function Match requires a range object as the second argument, not an array

That's not true JoeMo, as shown here:

Sub Test()
    Dim arr_years As Variant
    arr_years = Array(2012, 2013, 2014)
    MsgBox Application.Match(2013, arr_years)
End Sub

 

[JoeMo]

That's not true JoeMo, as shown here:

Sub Test()
    Dim arr_years As Variant
    arr_years = Array(2012, 2013, 2014)
    MsgBox Application.Match(2013, arr_years)
End Sub

Thanks for that correction Andrew, I learned something new! The description of the Match function in Excel Help indicates the lookup array should be a range of cells, but your routine proves otherwise.

 

[wCJanssen]

I’ve found a solution that works, using the DistinctValues-formula from CPearson.com:

Private Sub cmd_try_Click()
Dim dataRange As Range
Dim oneCell As Range
Dim newYear As String
Dim arr_years() As Variant
Dim i As Integer
 
Set dataRange = Range(Sheets("Overzicht_aanmeldingen").Cells(4, 10), Sheets("Overzicht_aanmeldingen").Cells(4, 10).End(xlDown))
For Each oneCell In dataRange
i = i + 1
newYear = CStr(Year(oneCell.Value))
ReDim Preserve arr_years(1 To i)
arr_years(i) = newYear
Next oneCell
'verwijder identieke items
arr_years = DistinctValues(arr_years, True)

Thanks for the input.

 

[newbie334]

FirstTeam = Left(Fullmatch, LinePos - 1)

The above code also gives runtime error 5. Beneath the full code. Any ideas where I went wrong here ?

Private Sub CommandButton1_Click()

Dim Fullmatch As String, FirstTeam As String, SecondTeam As String
Dim LinePos As Integer
Dim i As Range

For Each i In Sheet1.Range("B8:B58")

Fullmatch = Sheets("matches").Range("B" & i.Row).Value
LinePos = InStr(Fullmatch, "-")

FirstTeam = Left(Fullmatch, LinePos - 1)
SecondTeam = Right(Fullmatch, Len(Fullmatch) - Len(FirstTeam) - 1)

Sheets("matches").Range("H" & i.Row).Value = FirstTeam
Sheets("matches").Range("I" & i.Row).Value = SecondTeam


Next

Sheets("matches").Range("B5:H55").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

End Sub

 

[Andrew Poulsom]

What is the value of LinePos when you get the error? Maybe it's zero because there is no - in Fullmatch.

 

[newbie334]

Yes that seemed to be the case. There where some empty columns which probably caused the error. Thanks for your help.