A set of Excel/VBA procedures that can be used to parse a Matrix into a List.Using Excel Range Names to Parse a Matrix

A set of Excel/VBA procedures that can be used to parse a Matrix into a List.Using Excel Range Names to Parse a Matrix

%d bloggers like this:

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

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

Regards

Alfred

LikeLike