CODEWITHSUNDEEP
excelvbaexcel-formula
EricG
EricG

Reputation: 65

Excel function CELL - INDEX - MATCH use converted to VBA

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
    ...

Upvotes: 0

Views: 76

Answers (1)

Ferdinando
Ferdinando

Reputation: 964

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

12/27/2016
12/28/2016
12/29/2016 
01/01/2017 
01/02/2017 
01/03/2017 
01/04/2017
01/05/2017 
01/06/2017 
01/07/2017 
01/08/2017 
01/09/2017 
01/10/2017

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

Upvotes: 1

Related Questions