VBA copy down using relative references

[SueKi]

Hi, I have a few different items that I typically add to an assortment of reports. I create a macro that inserts a column where I have my cursor, name the column, then put the formula I need in the top cell. My macro ends there I double click to copy it down. It works, but I I recently learned to do the copy down part if I know what column I'm in, it would be great to add that last step to my macros, but I don't know how to reference it right. Can someone help me to correct this so it works? Column A has no blank rows, I need to put the formula in whatever column my macro is at when it reaches this bit....

LastRow = Range("a" & Rows.Count).End(xlUp).Row

Range(ActiveCell.Column() & LastRow).Formula = "=IF(RC[-1]<TODAY(),""PD"",ISOWEEKNUM(RC[-1]))"

Thanks in advance!

 

[Joe4]

Welcome to the Board!

Try something like this:

Dim c as Long
Dim LastRow as Long

'Get current column
c = ActiveCell.Column

'Find last row in column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Populate formula from row 1 to last row
Range(Cells(1, c), Cells(LastRow, c)).FormulaR1C1 = "=IF(RC[-1]<TODAY(),""PD"",ISOWEEKNUM(RC[-1]))"

 

[SueKi]

Works like a charm! Thank you so much! You have no idea how often I have to insert little things to consolidate data like this one, or identify customer groups. I'll use this constantly!

 

[SueKi]

Welcome to the Board!

Try something like this:

Dim c as Long
Dim LastRow as Long

'Get current column
c = ActiveCell.Column

'Find last row in column A
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Populate formula from row 1 to last row
Range(Cells(1, c), Cells(LastRow, c)).FormulaR1C1 = "=IF(RC[-1]<TODAY(),""PD"",ISOWEEKNUM(RC[-1]))"

Oh, and I'm so excited to find such an active board, it seems like a great resource.

 

[Joe4]

You are welcome.
Glad I was able to help!