Macro to fill first empty column

[Melzebu]

Hi everyone,

I have a table with few headers and I would like to create a macro that fills the cells of the first empty column based on the length of another column and its content based on a third column.
I'll try to explain a little bit better.

Let's say I have a worksheet called "Report" with a table, and the headers of this table are:

Case Number

Status

Severity

Priority

Subject

Date/Time Opened

Date/Time Closed

FRT

respectively from column A to column H

I would like to add another column (in this case I) with header "Year - Month" and the formula that takes input from "Date/Time Opened" column and reports in text only the year-month.
I created the following macro:

Range("I1").Select
ActiveCell.FormulaR1C1 = "Year - Month"
Range("I2:I" & Cells(Rows.Count, "H").End(xlUp).Row).FormulaR1C1 = "=TEXT([@[Date/Time Opened]],""yyyy-mm"")"

This does its job until I select I1 and run it but I would like to change first that the range.select is not I1 but the first empty column of the worksheet "Report" and obviously that the range of cells - Range("I2:I" & Cells(Rows.Count, "H") - is the column selected, so the first empty column.

I need this because the table may have more columns than from A to H so the in some cases the range could be column I in other cases it could be column K or J.

I hope I explained the point.

Let me know please if there's something I can do to specify those parameters.

Thank you all.

 

[rollis13]

Have a try with:

Dim LC     As Long
Dim LR     As Long
LC = Cells(1, Cells.Columns.Count).End(xlToLeft).Column + 1 'find first empty column
Cells(1, LC).FormulaR1C1 = "Year - Month"
LR = Cells(Rows.Count, "H").End(xlUp).Row
Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=TEXT([@[Date/Time Opened]],""yyyy-mm"")"

 

[Melzebu]

Worked like a charm, thank you @rollis13

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.