Argthtjtr

I am trying to implement a .Match function as array with a variable range in VBA, to find the first non-zero cell in a specific range.

However, currently I only receive a runtime error 1004.

Any help is much appreciated! Thanks

Set myRange = Worksheets("Portf_Mod").Range("AB368:CY368") 

With Application.WorksheetFunction

    Date_col = .Match(True, [myRange <> 0], 0)

End With

As you are using VBA, instead of using Match you could use the API to make for a more explicit and maintainable code:

Function FirstNonZeroCell(rng As Range) As Range

    Dim cell As Range

    For Each cell In rng.Cells

        If cell.Value <> 0 Then

            Set FirstNonZeroCell = cell

            Exit Function

        End If

    Next

End Function

Or if you want a more compact version (but less readable in my opinion):

Function FirstNonZeroCell(rng As Range) As Range

    For Each FirstNonZeroCell In rng.Cells

        If FirstNonZeroCell.Value <> 0 Then Exit Function

    Next

    Set FirstNonZeroCell = Nothing

End Function

Here is a sample of use:

Sub test()

    Dim res As Range

    Set res = FirstNonZeroCell(Range("A1:Z100"))

    If Not res Is Nothing Then

        MsgBox "Found value " & res.Value & " in cell " & res.Address & "."

    Else

        MsgBox "No match has been found!"

    End If

End Sub

Think the array element is throwing out your approach so here is an alternative.

It's not clear if you want the first non-zero value or its position so this covers both.

Date_col = Evaluate("MATCH(TRUE," & myRange.Address & "<>0,0)") 'returns position

Date_col = Evaluate("INDEX(" & myRange.Address & ",MATCH(TRUE," & myRange.Address & "<>0,0))") 'returns value

It's the first time I came across an "array formula" in VBA. This is not wrong, but not very evident to understand/troubleshoot. On my machine [where the range had all empty cells, it gives an error [myRange <> 0] = Error 2092, then within the Match function, then gets another error...

The array formulas are great in a formula worksheet only, but I feel you should avoid them in VBA. You have the ability to use loops in VBA, so, don't hesitate to use them! Having your train of though as explicit as possible is key when writing software (so you will understand it later!).

My suggestion would be this :

Option Explicit



Function FindDateColumnInRange(ByVal RangeToLookIn As Range) As Long



If RangeToLookIn.Rows.Count <> 1 Then

    'The range you're looking in has more than one row

    'what should you do in this case? Look only in the first row?



Else



    Dim i As Long

    'The range has only one row

    For i = 0 To RangeToLookIn.Columns.Count - 1

        If RangeToLookIn.Item(1, i).Value <> 0 Then

            'should you verifiy that the value is a date value?

            FindDateColumnInRange = RangeToLookIn.Item(1, i).Column

            Exit Function

        End If

    Next

End If



'the range didn't have a value different from 0

FindDateColumnInRange = 0



End Function

In action, you would get :

Sub Test()



Dim MyRange As Range

Set MyRange = Worksheets("Portf_Mod").Range("AB368:CY368")

Dim date_col As Integer



date_col = FindDateColumnInRange(MyRange)



If date_col = 0 Then

    'There was no date in your range

End If



End Sub

Yeah, it's a lot more long than SJR suggestions, but, it covers every exceptions and you can control how to look if you were to pass a multidimensionnal array (iterate through rows first then columns, or the other way around).

I wouldn't use the Match function for this...

Set myRange = Worksheets("Portf_Mod").Range("AB368:CY368") 

On Error Resume Next

Date_col = myRange.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns).Column

On Error GoTo 0