Only need certain part of each cell

[pretzel]

Hello experts,

I have a list of dates and I only need the month of each date. I am still learning on VBA so please bear with me. Below is the code I have written but it seems that it is only extracting the month of the first cell. Where have I done wrong? Also, I may need to increase the maximum number of i from 20 to 50 (or to infinity) if there are more new items in column B. Is there any way to improve the code? My concern is that if i were to increase to say 5000, the VBA would loop forever.

Dim m As Range
Dim i As Integer

Dim intMonth As Integer
Dim intDay As Integer

For Each m In Range("B3", Range("B3").End(xlDown))
intMonth = DatePart("m", Range("B3"))
intDay = DatePart("d", Range("B3"))

For i = 3 To 20
Cells(i, "D").Value = intMonth
Next i
Next m

Thank you

 

[WarPigl3t]

Your code doesn't do anything. Please tell me what you want the code to do in plain english. Your loop doesn't output anything.

 

[WarPigl3t]

Disregard my last message. I misread it.

lastRow = Range("B" & Rows.Count).End(xlup).Row
i = 3
Do Until i > lastRow
    Range("D" & i).Value = DatePart("m", Range("B" & i).Value)
    i = i + 1
Loop

I like Do Loops better than For Loops.

 

[Fluff]

Hi, give this a go.

Sub Pretzel()

    Dim m As Range
    
    For Each m In Range("B3", Range("B3").End(xlDown))
        m.Offset(, 2) = DatePart("m", m)
    Next m

End Sub

Rather than running 2 loops with variables, it's easier to just write the data straight off as above.
An even better way to do this would be to enter in cell D3 =Month(B3) and then copy down

 

[pretzel]

Hi, give this a go. Rather than running 2 loops with variables, it's easier to just write the data straight off as above.
An even better way to do this would be to enter in cell D3 =Month(B3) and then copy down

Wow cool. It's much more neater. However, when it runs, there is an "application or object defined error" that stucks at cells(i,"D"). What does it mean? Isn't it the right way to direct the results to the destination cell?

    Dim m As Range
   
    For Each m In Range("B3", Range("B3").End(xlDown))
        m.Offset(, 2) = DatePart("m", m)
    Next m


    Dim i As Integer
      For i = 3 To 20
[COLOR=#daa520][B]    Cells(i, "D").Value = m[/B][/COLOR]
    Next i

 

[Fluff]

You Don't need that part of the code. The

m.Offset(, 2) = DatePart("m", m)

Gets the month number & writes it to column D

 

[Peter_SSs]

A couple of comments.

1. If you want, you can do all the rows at once, rather than looping through one-by-one

Sub Get_Month()
  Range("D3:D" & Range("B3").End(xlDown).Row).Value = Evaluate("if(row(),month(" & Range("B3", Range("B3").End(xlDown)).Address & "))")
End Sub

2. Do you really need a macro? This formula in D3 (copied down) would also do it.

=MONTH(B3)