VBA autofill used range

[blackie42]

Hi,

I recorded macro to automatically get month end.

I want to autofill to last used cell in column G which will vary.

Code copies down and formats date but also copies "31/01/1990" down to row 2000


Sub Macro9()
Range("G12").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(RC[-5],0)"
Range("G12").Select
Selection.AutoFill Destination:=Range("G12:G2000")
Columns("G:G").Select
Selection.NumberFormat = "m/d/yyyy"
Range("H12").Select
End Sub


Any way to do this correctly

Thanks in advance
Jon

[Logit]

.
Please explain in more detail what the goal is.
If the macro works as desired, what would be displayed in Col G12:G2000 ?

[blackie42]

Hi

Thanks for reply

So what I wanted to do was for the macro to initially put the formula =EOMONTH(C12,0) in to G12 and then
copy this down until the first blank cell in column G.

I need to reformat the column to date but in selecting the whole column the date is copied correctly in to the cells that have EOMONTH in but also populates
blank cells down to G2000 with the date "31/01/1990"

I need these unpopulated cells to remain blank

sorry if not clear

thanks
Jon

[mana]

Sub test()

With Range("B12", Range("B" & Rows.Count).End(xlUp)).Offset(, 5)
.FormulaR1C1 = "=EOMONTH(RC[-5],0)"
.NumberFormat = "m/d/yyyy"
End With

End Sub

[blackie42]

Thanks very much mana - works really well

regards
Jon

[blackie42]

Hi Mana.

I have another similar problem.

I have a date in cell B1.

Following on from the code that inserts the EOMONTH (which you kindly provided) I also need to apply a formula =NETWORKDAYS(G12,$B$1) in the adjacent cell (so H12 etc) & copy down to all cells in column H where there is a date in column G.

Please can you help further

thanks
Jon

[blackie42]

Hi

Used a loop which seems to work ok


Sub Macro11()
Range("J12").Select

Do

ActiveCell.FormulaR1C1 = "=ABS(RC[-3])"
ActiveCell.NumberFormat = "#,##0.00"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(0, -3).Value = ""

End Sub

[blackie42]

Sub Macro13()
Range("I12").Select

Do

ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-1],R1C2)"
ActiveCell.NumberFormat = "0"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Offset(0, -3).Value = ""

End Sub

[mana]

Loop isn't necessary



Sub test()
Dim r As Long

r = Range("F" & Rows.Count).End(xlUp).Row

With Range("I12:I" & r)
.Formula = "=NETWORKDAYS(H12,$B$1)"
.NumberFormat = "0"
End With

End Sub