Excel function CELL - INDEX - MATCH use converted to VBA

Question

I have a list of ascending dates. I am using the following formula to return the cell reference:

=CELL("address",INDEX(A2:A14,MATCH(D1,A2:A14,1)))

This formula gives me the result I what e.g. a search for 1/2/2017 returns the cell reference of $A$6. While a search for 12/30/2016 returns the cell reference of $A$4.

How would I accomplish the same result using VBA, while returning the next date value in the list if the search date is greater than the previous date in the list and another date that is greater than the search date exist?

 Dates                 1/2/2017
  12/27/2016              $A$6
  12/28/2016
  12/29/2016
  1/1/2017
  1/2/2017
  1/3/2017
    ...

in VBA you can use Application.Match() in a similar way to how you use MATCH() on a worksheet. Likewise Application.Index() — Tim Williams

Ferdinando Ferdinando · Accepted Answer · 2018-10-14T22:35:48

If i anderstood your question i have an alternative for you. I have tested this code and works fine.

I have a list of ascending dates from A2 to A14 I use cells(1,4) [in the sheet is D1] as input data to compare into list. The result of comparison is into cells(2,4) [in the sheet is D2]

i.e. my list from A2 to A14

into cells(1,4) i wrote 01/05/2017

output macro is into cells(2,4) and is :$A$9

If i write 01/11/2017 the result is $A$14

Sub test()
Dim i, start, finish As Integer
Dim myDate,output As Date
i = 0
start = 2
finish = 14
myDate = Cells(1, 4) ' my input - cells(2,4) is output

For i = finish To start Step -1
    If (i = start) Then
        Cells(i, 1).Activate 'cell where is "my date"
        Cells(2, 4) = ActiveCell.Address ' get the address -output
        'Exit For
    End If
    If myDate >= Cells(i, 1) Then 
        Cells(i, 1).Activate 'cell where is "my date"
        Cells(2, 4) = ActiveCell.Address ' get the address -output
        Exit For
    End If
Next i
End Sub

Excel function CELL - INDEX - MATCH use converted to VBA

1 Answers