CODEWITHSUNDEEP
vbaexcel
Gabba48
Gabba48

Reputation: 25

Index(Match()) in VBA Excel with Formulas in Cells

First time here. Pretty new to coding in general. Just doing this for fun/trying to learn. Appreciate the help in advance.

I'm trying to have my code look at a CSV cell and pull out the values into an array. Then, using that array, Index(Match()) to get dates in the last column into a new array. Then I will look at the latest date and write that into the cell next to CSV cell.

enter image description here

This is what I have so far:

Dim DepArray As Variant, FinArray As Variant, x As Integer, i As Integer, y As Variant, StartDate As Date
    DepArray = Split(Target, ",")
    x = UBound(DepArray)
    'INDEX(MATCH()) to write to new array of finish dates for prior tasks
    For i = 0 To x
        y = Application.WorksheetFunction.Index(Sheets("SH1").Range("A1:E4").Value, Application.Match(DepArray(i), Sheets("SH1").Range("A1:A4").Value, 0), 5)
        FinArray(i) = y
    Next i
    StartDate = Application.WorksheetFunction.Max(FinArray)
    Target.Offset(0, 1).Value = StartDate

I am getting a type mismatch error on the y=Index(Match()) line. I've tried every variable type, I think, declaring y alternatively as an integer and a date. Any thoughts?

FYI I'm using the .Value callouts because in both column A and E, the item numbers and dates are being brought in by functions in the cells.

Also, if you have tips for cleaning up the code or see other things wrong I haven't gotten to yet, I'd love to learn better practices.

Thanks!

Updated code to help with checking. I think I'm getting the Match() right because now the error shows on line MsgBox y(i). I've also tried it as MsgBox y

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
    Dim DepArray As Variant, FinArray As Variant, x As Integer, i As Integer, y As Variant, StartDate As Date
    DepArray = Split(Target, ",")
    MsgBox DepArray(0)
    x = UBound(DepArray)
    MsgBox x
    MsgBox DepArray(x)
    'INDEX(MATCH()) to write to new array of finish dates for prior tasks
    For i = 0 To x
        MsgBox DepArray(i)
        y = Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0)
        MsgBox y(i)
        'FinArray(i) = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("A1:E4").Value, Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0), 5)

    Next i
    StartDate = Application.WorksheetFunction.Max(FinArray)
    Target.Offset(0, 1).Value = StartDate
End If


End Sub

Upvotes: 2

Views: 1161

Answers (1)

David Zemens
David Zemens

Reputation: 53663

This line creates DepArray as an array of string:

DepArray = Split(Target, ",")

Because Split returns a string array.

Later, when you do:

y = Application.Match(DepArray(i), Sheets("Sheet1").Range("A1:A4").Value, 0)

The Match function returns the 1004 error because there is no value of "1" in that range, instead there's a value of 1!

You could probably resolve by casting to long/integer:

Dim valueToMatch as Long
valueToMatch = CLng(DepArray(i))
y = Application.Match(valueToMatch, Sheets("Sheet1").Range("A1:A4").Value, 0)

It's also usually a good idea to trap errors, like:

If IsError(y) Then
    MsgBox "Unable to find " & CStr(valueToMatch)
    ' Exit sub, or go to next iteration, etc.
End If

You also need to ReDim the FinArray, after this line:

x = UBound(DepArray)
ReDim FinArray(x)

And uncomment the line that assigns values into this array, otherwise it remains an Empty variant, and Max(Empty) will return 0 every time :D

Also, you need to do Application.EnableEvents = False at the beginning of your procedure and then Application.EnableEvents = True at the end of the procedure, in order to avoid possible infinite loop, since Target.Offset(0, 1).Value = StartDate will trigger the Change event :)

Upvotes: 1

Related Questions