extraction month (in loop)

[Forestq]

Hi,

I want from each cells in column A:

A1: 30-Jan-2011
A2: 11-Oct-2010
A3: 12-Jun-2011
A4: 01-Aug-2011

extract Month. It's like:
B1: =MONTH(A1)
B2: =MONTH(A2)
B3: =MONTH(A3)
B4: =MONTH(A4)

Now I need to write macro to do it.
I have some draft but need to modify:

With Range("B1")
 .FormulaArray = "=Month(A1)"
End With

Please help
PvK

 

[Andrew Poulsom]

More like:

With Range("B1:B4")
     .Formula = "=MONTH(A1)"
End With

 

[VoG]

Try

Sub test2()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:B" & LR).Formula = "=MONTH(A1)"
End Sub

 

[Forestq]

thanks, cool!

I also made like this:

Sub form()
x_rows = Application.WorksheetFunction.CountA(Columns(2))
Range("B1").Select
ActiveCell.Formula = "=Month(A1)"
Selection.AutoFill Destination:=Range("B1:B" & x_rows), Type:=xlFillDefault
End Sub