Converting date from cell to month as mmm VBA

[saadn]

Hi,

I've got dates in column A in dd/mm/yyyy format and I need to extract the month number in mmm format in column H vba. I've used the following code to get the date into column H but cannot get it in mmm format.

Dim y As Integer
On Error Resume Next
For y = 1 To 10


If Not Cells(y, 1) = "Date" And Cells(y, 8) = "" Then
Cells(y, 8).Select
ActiveCell.Value = Cells(y, 1)

The reason I need to do this is each month bank statement data will be pasted into a tab and based on the month selected on the control sheet, the transactions for that particular month will be split between payments and receipt and moved to tabs called "payments" and "receipts" respectively. I was going to use the above code to extract the month from the date and based on what month is selected in the control sheet, write coding to pick up a transaction, work out if it is a payment and receipt and then move it. If there is a better way I can do this, that would help.

 

[shg]

cells(y, "H") .value = format(cells(y,"A").value, "mmm")

 

[shg]

Or

cells(y, "H").value = cells(y,"A").value
cells(y, "H").numberformat = "mmm"

 

[saadn]

Or

cells(y, "H").value = cells(y,"A").value
cells(y, "H").numberformat = "mmm"

Thanks but this is converting the "dd" to the month so if the date is 01/04/2018 (1 April 2018) it is setting the "mmm" as "Jan".

 

[saadn]

Also, I need to final cell to actually contain "Apr" as text and not as a date formatted as "mmm"

 

[MARK858]

Try...

Cells(y, "H").Value = Format(CLng(Cells(y, "A").Value), "mmm")

 

[saadn]

Try...

Cells(y, "H").Value = Format(CLng(Cells(y, "A").Value), "mmm")

doesn't seem to be doing anything?

I've got

Dim y As Integer
On Error Resume Next
For y = 1 To 10




If Not Cells(y, 1) = "Date" And Cells(y, 8) = "" Then
Cells(y, 8).Select
ActiveCell.Value = Cells(y, 1)
Cells(y, "H").Value = Format(CLng(Cells(y, "A").Value), "mmm")


Else
End If
Next y

 

[MARK858]

This code

Sub vvvv()
    Dim y As Integer
    For y = 1 To 10

        If Not Cells(y, 1) = "Date" And Cells(y, 8) = "" Then
            Cells(y, "H").Value = Format(CLng(Cells(y, "A").Value), "mmm")
        End If
    Next y
End Sub

gives me this result

Excel 2010
	A	B	H
1	01/04/2018		Apr
2	01/05/2018		May
3	Date
4	01/07/2018		Jul
5	01/08/2018		Aug
6	01/09/2018		Sep
7	Date
8	01/11/2018		Nov
9	01/12/2018		Dec
10	Date
Sheet2

When column H was blank.

 

[saadn]

I copied and pasted that and am getting "runtime error 13 type mis-match" which highlights
"Cells(y, "H").Value = Format(CLng(Cells(y, "A").Value), "mmm")"

edit: it's because the format of my date range is not "short date". I need to put a code in to do that first I guess

 

[MARK858]

Put =ISNUMBER(A3) in a blank cell (change A3 to another cell in the range if it isn't a cell with a date in it).
Does it return TRUE or FALSE?