CODEWITHSUNDEEP
vbaexceldateexcel-formulamultiple-columns
cthallofamer
cthallofamer

Reputation: 141

Excel VBA: How to extract date from all cells of a column and put text into another column?

I am still new to VBA and need help with my code. I am currently attempting to code multiple macros in one actual macro. I am trying to get all the dates starting from b5 from one column, Column B, and put them into another column, Column A, starting from A5. The thing is, all the dates in Column B are in (01/24/17) type format, when I need just the month name in text in Column A. I also need the formula to continue until the last cell with a value in Column B. Here is what I have so far and it doesn't seem to be working:

Sub Macro7()
    Worksheets("mac2").Range("A5:A1000").Formula = "=text(if($B5>0,$B5,"""")"
End Sub

Again, I am very new to VBA and am aware that hardcoding ranges and such isn't the best idea. If you could also direct me towards a site that teaches basic VBA that would be much appreciated.

Upvotes: 1

Views: 1518

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Maybe , if your column B values are text then the code like this

Sub Macro7()
Dim vDB, vR()
Dim i As Long, y As Integer, m As Integer, d As Integer
Dim R As Long
    With Worksheets("mac2")
        vDB = .Range("b5", .Range("b" & Rows.Count).End(xlUp))
    End With
    R = UBound(vDB, 1)
    ReDim vR(1 To R, 1 To 1)
    For i = 1 To R
        If vDB(i, 1) > 0 Then
            vR(i, 1) = Format(vDB(i, 1), "mmmm")
        End If
    Next i
    Worksheets("mac2").Range("a5").Resize(R) = vR
End Sub

Upvotes: 1

Related Questions