Array with dates

fizzyh · Feb 10th 2015

I need some help in this guys, I have an existing column fill with date and I want to add another column beside it with values depending on the date. For example from from 01/01/2015 to 02/15/2015 as January and so on. In this particular format, I tried using codes from my other projects but it does not work. Here are the code:

Code

Sub Month()
     Dim Found As Range
     Dim LR As Long
     Dim WS As Worksheet
     Dim cell As Range
     Dim a As Variant, v As Variant, num
     Set WS = Sheets("PAYABLES - OUTFLOWS")
     Set Found = WS.Rows(1).Find(What:="PAYABLES - OUTFLOWS", _
                                LookIn:=xlValues, lookat:=xlWhole)
     If Found Is Nothing Then Exit Sub
     a = [{"01/18/2015", Jan; "01/19/2015", Jan]
     LR = WS.Cells(WS.Rows.Count, Found.Column).End(xlUp).Row
     Found.Offset(0, 2).EntireColumn.Insert
     WS.Cells(1, Found.Column + 2).Value = "Month"
     
       For Each cell In WS.Range(WS.Range("A1"), WS.Cells(LR, 3))


         v = Application.VLookup(cell.Value, a, 2, False)
        cell.EntireRow.Cells(Found.Column + 2).Value = IIf(IsError(v), "", v)
     Next cell
    
End Sub

Display More

ashu1990 · Feb 10th 2015

Re: Array with dates

Code

a = [{"01/18/2015", Jan; "01/19/2015", Jan]

you have used "a" as array where as you are using "v" for output value?

Code

for each cell in WS.Range(WS.Range("A1"), WS.Cells(LR, 3)) 
        cell.EntireRow.Cells(Found.Column + 2).Value = IIf(IsError(v), "", v) 
 next cell

fizzyh · Feb 11th 2015

Re: Array with dates

Hi. Sorry I have missed out a line while posting. I have edited it. And what i am trying to achieve is for example in column A, there will be a set of dates from 01/01/2015 until 12/31/2015. What i am trying to do is to create a column and base on the dates on column A to give the new column value. And yes the pattern would be from 01/15/2015 - 02/15/2015 as january.
02/16/2015 - 03/15/2015 as february. and it goes on.

Thanks.

ashu1990 · Feb 12th 2015

Re: Array with dates

just use this and change the range as per your's :smile:

Code

For Each cell In Range("A2:A51")
If Day(cell) < 15 Then
cell.Offset(0, 1).Value = Format(DateAdd("m", -1, cell), "MMM")
Else
cell.Offset(0, 1).Value = Format(cell, "MMM")
End If
Next cell

Array with dates

Participate now!

Share