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