VBA - Naming Columns according to Specified Range

[salman94]

Hi,

I have a range of dates in column A from A18 to end (the number of rows will vary) and I've named the range "TradeDates". For each date in that range, I'd like to have a column starting from column J displaying the date on the top and then 3 columns to the right, I'd like the next date to be displayed, then next trade date 3 columns to the right of that and so on. How can this be done in VBA?

 

[Norie]

Are the dates in column A repeated?

 

[salman94]

Are the dates in column A repeated?

Thanks for responding. No I have already wrote a macro to remove the duplicates and sort them in order from earliest to latest.

 

[Norie]

Perhaps.

Dim rngTradeDates As Range
Dim rngCol As Range
Dim cl As Range

   Set rngTradeDates = Range("TradeDates")

   Set rngCol = Range("J1")

   For Each cl In rngTradeDates.Cells

       rngCol.Value = cl.Value
       Set rngCol = rngCol.Offset(,3)

   Next cl

 

[Fluff]

How about

Sub addheaders()
   Dim ary As Variant, Nary As Variant
   Dim i As Long, j As Long
   
   ary = Range("TradeDates").Value2
   ReDim Nary(0 To UBound(ary) * 3)
   For i = 1 To UBound(ary)
      Nary(j) = ary(i, 1)
       j = j + 3
   Next i
   Range("J1").Resize(, UBound(Nary)).Value2 = Nary
End Sub

This will put the dates in row 1

 

[salman94]

Perhaps.

Dim rngTradeDates As Range
Dim rngCol As Range
Dim cl As Range

   Set rngTradeDates = Range("TradeDates")

   Set rngCol = Range("J1")

   For Each cl In rngTradeDates.Cells

       rngCol.Value = cl.Value
       Set rngCol = rngCol.Offset(,3)

   Next cl

This worked thanks!