Using Excel Range Names to parse a Matrix into a List

2 thoughts on “Using Excel Range Names to parse a Matrix into a List”

1. I’d prefer a tiny macro in this case:

   Sub M_snb()
sn = Sheets("forex 2D Table").Cells(1).CurrentRegion

   ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)

   sp(0, 0) = "CCYUS"
   sp(0, 1) = "Date"
   sp(0, 2) = "Value"

   For j = 1 To UBound(sp)
   y = (j - 1) \ (UBound(sn, 2) - 1) + 2
   x = (j - 1) Mod (UBound(sn, 2) - 1) + 2
   sp(j, 0) = sn(1, x)
   sp(j, 1) = sn(y, 1)
   sp(j, 2) = sn(y, x)
   Next

   With Sheets("forex ccyus_date_value").Cells(1, 10)
.Resize(UBound(sp) + 1, Ubound(sp, 2) + 1) = sp
.CurrentRegion.Sort .Offset, , , , , , , True
End With
End Sub

   LikeLike

   Reply

2. Thanks for sharing. You present a number of interesting vba coding techniques.
   Regards
   Alfred

   LikeLike

   Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.