Is there a way to copy the formatting of a range into an array?
Focusing on the question as posed, yes, that can be done. Unfortunately, it can't done in a one-liner, in the way that a range's values can be assigned to an array with myArray = Range("A2:F25"), for example. (See, also, brettdj's comment.) Instead, you'd need something like:
Dim rng as Range
Dim formatArray() As String
Dim i as Long, j as Long
Set rng = Range(A2:F20) 'or whatever the range is
Redim formatArray(1 to rng.Rows.Count, 1 to rng.Columns.Count)
For i = 1 to rng.Rows.Count
For j = 1 to rng.Columns.Count
formatArray(i, j) = rng.Cells(i, j).NumberFormat
Next
Next
...
A couple of observations, though:
You actually only need to know the formatting for a single row of the range since, presumably, the number formatting in a column will not change mid-column.
That would simplify the code to:
...
Redim formatArray(1 to rng.Columns.Count)
For i = 1 to rng.Columns.Count
formatArray(i) = rng.Cells(1, i).NumberFormat
Next
...
assuming, for sake of example, that row 1 of the range has the necessary number formats.
I am curious why you would want to modify a formula on the worksheet so that it will round, since you could presumably do the calculation in your code, with rounding, and then write the resulting value back to the sheet for your report.
If you need to apply number formats to the values you are writing to the worksheet (not just modify formulas so that they will produce whole numbers), that can be done to whole columns within the range at once, i.e.,
...
For i = 1 to rng.Columns.Count
rng.Columns(i).NumberFormat = formatArray(i)
Next
...
If you need to convert the results of a cell's formula to a rounded value, you can do that with something like
rng.Cells(2, 5).Value = WorksheetFunction.Round(rng.Cells(2, 5).Value, 0)
to give an example for a single cell. This assumes, of course, that the data feeding into the formula are already in the sheet and that the formula has been recalculated.
