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

Question

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.

As for the dynamic end see [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for info on finding the last cell. — Scott Craner, Jun 12 '17 at 14:18
Sorry the whole formula should be `"=if($B5>0,text($B5,""mmmm""),"""")"` — Scott Craner, Jun 12 '17 at 14:24

Dy.Lee · Accepted Answer · 2017-06-12T15:20:42.317

1

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

edited Jun 12 '17 at 15:20

answered Jun 12 '17 at 14:42

Dy.Lee

7,527
1
12
14

thanks for the quick response. when I tried running that it said "run time error '6' Overflow" – cthallofamer Jun 12 '17 at 14:48
Is your day (01/24/17) text value or date value? – Dy.Lee Jun 12 '17 at 14:50
and "(" is exist or none? – Dy.Lee Jun 12 '17 at 14:51
its date value. and no there aren't parenthesis in column B – cthallofamer Jun 12 '17 at 14:55
I didn't get an error this time but nothing happened. my worksheet still looks the same – cthallofamer Jun 12 '17 at 15:10
Is there datas in column B? – Dy.Lee Jun 12 '17 at 15:17
update: so I put the code in the individual vba sheet for "mac2" and it worked. but it doesn't work when I try to run my whole macro which contains multiple worksheets. it doesn't work when I use this: Sub RunAll() Call AddWorksheet Call sbCopyRangeToAnotherSheet Call Filters Call macro4 Call CopyData Call Macro7 End Sub – cthallofamer Jun 12 '17 at 15:19
Worksheets("mac2").Range("a5").Resize(R) = vR sheet is missed. I modified code. – Dy.Lee Jun 12 '17 at 15:21
It worked! Thank you so much for your help and dedication. You're a life saver! – cthallofamer Jun 12 '17 at 15:24
Good luck! I'm sure you will do well. – Dy.Lee Jun 12 '17 at 15:27

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

1 Answers1