Excel VBA Assign Index Formula to a variable

Question

I am trying to set a combination of INDEX and MATCH formula results to a variable instead of set the results to a cell.

The code that I have so far is as below:

Dim todayDate as Date
Dim startDate as Date

todayDate = Format(Date, "MM/dd/yyyy")
startDate = "=INDEX([Financial_Calendar.xlsx]Calendar!C2,MATCH(CLng(todayDate.Value),[Financial_Calendar.xlsx]Calendar!C9,0))"

The startDate formula will find the todayDate inside the excel file and return the value of the startDate for todayDate row. The startDate value is in Date form.

But now I still did not able to get the value returned. Can anyone help me on this ?

Answer 1

To start, you need to assign the date that you want to look up to todayDate to use in the worksheet function match. for example, todayDate = ThisWorkbook.Sheets(1).Range("A1").Value, if you've stored the date in A1 of the first sheet of your workbook.

Next, if you want to use a worksheet function in VBA you need to specify that:

rowNumber = Application.WorksheetFunction.Match(arguments)

(https://docs.microsoft.com/en-us/office/vba/excel/Concepts/Events-WorksheetFunctions-Shapes/using-excel-worksheet-functions-in-visual-basic)

and the same for the index funciton, although it might be cleaner to use the .cells property in VBA directly. (https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-cells-by-using-index-numbers)

hope this helps you get