Write Data After Last Cell Used in a Row and do a vlookup going down the column until a certain cell

Question

I am trying to automate an excel process with vba.

There is a column for each month starting at 2010 (this can extend infinitely, or at least within excel's limit). There are some 500 rows below that with numerical data.

So, I would like to find the last column which isn't used (current month) and populate all of the rows under it from a vlookup.

Thanks.

Working Code!

With ActiveSheet.Range("IV42").End(xlToLeft)
    Dim iRow As Integer
    For iRow = 1 To 581

        Dim Lookup_Value, Table_Array, Col_Index, Range_Lookup As String
        Lookup_Value = "B42:B622"
        Table_Array = "'Current Rankings'!$B$2:$C$607"
        Col_Index = "2"
        Range_Lookup = "False"
        .Offset(iRow - 1, 0).Formula = "=VLOOKUP(" & Lookup_Value & "," & Table_Array & "," & Col_Index & "," & Range_Lookup & ")"

    Next iRow
End With

Find last row, column, or cell: http://www.rondebruin.nl/last.htm — Jon Crowell, Sep 20 '12 at 13:23
Another link : http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column/11883425#11883425 — Siddharth Rout, Sep 20 '12 at 13:27
I saw a couple of these, but I think it is more complicated (or at least I am having trouble applying them). — Paul Shapiro, Sep 20 '12 at 16:09

score 2 · Answer 1 · edited Sep 21 '12 at 20:19

To find the last column, use ActiveSheet.Range("IV1").End(xlToLeft). (That is assuming you have values in the first row)

So, to find the column right after the last column used, you can use ActiveSheet.Range("IV1").End(xlToLeft).Range("B1"). That will point to the first cell of the new month's column.

Then you can do a simple loop to fill the column... Your code should look something like:

With ActiveSheet.Range("IV1").End(xlToLeft).Range("B1")
    Dim iRow as Integer
    for iRow = 1 to 500

        Dim Lookup_Value, Table_Array, Col_Index, Range_Lookup
        Lookup_Value = ????
        Table_Array = ????
        Col_Index = ????
        Range_Lookup = ????

        .Offset(iRow-1,0).Formula = "=VLOOKUP(" & "," & _
            Lookup_Value & _"," & _
            Table_Array & "," & _
            Col_Index_Num & "," & _
            Range_Lookup & ")"

    next iRow
end with

I got this working. Posted the source above. Thanks @FlatEarch! — Paul Shapiro, Sep 20 '12 at 16:32

Write Data After Last Cell Used in a Row and do a vlookup going down the column until a certain cell

1 Answers1