Using a Lookup at Next Available Line in Excel VBA

I am trying to implement a lookup feature in Excel Vba. I do not have any code to share because I am uncertain how to begin with the implementation.

Upon opening a workbook I want to use VBA to enter today's date into the next available row in column A - which I currently have working now. However, at that point in Column B on that same line, I to find a stock rate in a table I have, where J2 is the date and J3 is the price of the stock.

What I think I need is a formula where I can lookup the date I just added in th开发者_开发技巧is table and then retrieve the price relevant to that date. I understand Vlookups in Excel very well; it is I just do understand how to use a lookup here for each next available line.

Here is my code for the dates:

Dim rnum as integer

rnum = sheet17.usedrange.cells.rows.count +1

sheet17.cells(rnum, 1).value = date

I am seeking lookup functionality relative to (rnum, 2) as the next available line.

If you want to hardcode it, that'd be

sheet17.cells(rnum, 2).formula = "=vlookup(" & sheet17.cells(rnum, 1).address(false,false,xlA1) & ", $J:$K, 2, false)"

If you would prefer to use whatever formula is on the previous line,

sheet17.range(sheet17.cells(rnum-1, 2), sheet17.cells(rnum, 2)).FillDown

I'm assuming when you say "stock rate in a table" you mean "stock rate in a worksheet" and also assume that the values in column J contain the stock rates for the same stock. In other words, you are only matching on a date in that column and not the stock symbol AND the date. (Please let me know if I have these assumptions wrong).

That being, said you can try the following formula in column B:

=IF(A50<>"",INDEX(J:J,MATCH(A50,StockSheet!J:J,0) +1),"")

In this case, the formula is in cell B50 and assumes the new date is in A50. It says given the date value in cell J + n, give me the value in cell J + n + 1.

I added a small validation check to see if there was a value in A50, but you may want to go deeper than that.

Also, if you want to make the value in B50 static, then just use the following code:

Sub mySub()
    Dim x As Range 'I assume this range will be for your currentm, working worksheet

    Set x = Range("B50", "B50")
    x.Formula = "=IF(A50<>"""",INDEX(J:J,MATCH(A50,Codes!J:J,0) +1),"""")"
    x = x.Value
End Sub