0

I have the following macro in Excel

enter image description here

Sub IHIAddWeeks()
'
' IHIAddWeeks Macro
' ihi-table
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    ActiveCell.FormulaR1C1 = "=YEAR(RC[1])&WEEKNUM((RC[1]))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A5"), Type:=xlFillDefault

End Sub

After It looks like:

enter image description here

Now I AutoFill until A5 because A5 is the last nonempty raw. But this can change. How can I say : "AutoFill it until the last nonempty B column" ?

At next I like to insert a raw after A2 if A3>A2+1 and let the new A3=A2+1, and then compare A3 with A4 and so on until the B column is blank.

Can one help?

in the end table should look like this enter image description here

2 Answers 2

2

How can I say : "AutoFill it until the last nonempty B column" ?

The best way, assuming you are using at least Excel 2010 (maybe 2007 would work), is to turn the table into an Excel table before running the macro. Then, when you run the macro, it will autofill to the end of the table. However, this would only work if the column was empty before you run the macro.

I don't understand the second question I'm afraid. Do you mean that you want a formula of =R[-1]C+1 if the new cell would otherwise be 1 week after the previous row? If so, I can't see why you would want to make things that complex.

Or do you mean that you want the cell to be blank if it is the same as the previous row?

1
  • I updated the question. please take a look
    – Max Muster
    Jun 5, 2015 at 10:11
0

So, the slightly wonkier way of getting the answer (Not able to run this myself right now, sorry)

Turn on macro recorder. Select the top of the column that's next to the column you need to fill (make sure in your code that this isn't relative). Ctr+down, arrow over (make sure relative references are on) - ctr+shift+up to select it all, then alt+H+F+I+D - this autofills the equation all the way down to the bottom.

It isn't the actual code, but it describes how to get it.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .