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
– Tim Williams, Commented Oct 14, 2018 at 20:47

Ferdinando · Accepted Answer · 2018-10-14 22:35:48Z

1

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

answered Oct 14, 2018 at 22:35

Ferdinando

9621 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EricG Over a year ago

Ferdinando, many thanks. This logic does what I need. I guess sometime you have to walk away from the built-in function to accomplish the things you need.

Collectives™ on Stack Overflow

Excel function CELL - INDEX - MATCH use converted to VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related