VBA Excel Percent format in an array

Is there a way to save a specific format in an array? I'm trying to find the percent difference and the regular difference between numbers in two separate grids. I can do these things individually but when I try to put the answer in the same worksheet I run into errors.

As of now, I calculate the percent difference and then I select the grid where all my answers are and change the style and number format. However, when I try to show the difference as well (such as "percent difference / difference" the program returns a long decimal for the percent difference. I know that the reason is because the number format and style format are not saved into the actual array but I can't figure out how to fix it.

I've tried the following:

• Changing the format right before I create the slash and the difference
• Assigning the answer array to the current values in the active cell (the values are still the decimals, not the percent counterparts)
• Calculating percent difference and the difference, then changing the format (this was more of a faint hope than an actual attempt to change anything)

I can't remember if I tried anything else but I know it failed horribly.

***** Example ******

The user will send my program to a specific workbook that has two sheets with a grid of numbers on them. These grids have the exact same dimensions. My program will calculate the difference between these sheets (Sheet1 - Sheet2), the percent difference ((Sheet1 - Sheet2) / Sheet2), and both. These three calculations are presented on three separate sheets that the program creates. All of the numbers that the program produces will be in the cells th开发者_开发技巧at correspond to the cells where it got the numbers in the first place. Also, the third sheet (both percent and difference) are seen as "percent difference / difference". Now here's an example with test numbers.

Grid 1

12.0    5.0

2.0     10.0

Grid 2

6.0     7.0

1.0     3.0

Diff

6.0     -2.0

1.0     7.0

Percent Diff

100.00%     -28.57%

100.00%     233.33%

So the "Both" tab would look like:

100.00%/6.0     -28.57/-2.0

100.00%/1.0     233.33%/7.0

Sorry for not making this more grid like. I wasn't sure how to present it.

Thank you

Not sure if I completely follow, but I think you may just need the range object ".Text" method:

 dim rgNumValues as Range    ' cells formatted as 12.0, 5.0 etc
 dim rgPctValues as Range    ' cells formatted as 100%, -28.57% etc.

Then to create new cells that marry both formats

 dim rgNumAndPct as Range

 rgNumAndPct.Cells(1,1) = rgNumValues.Cells(1,1).Text + " / " _
                        + rgPctValues.Cells(1,1).Text

 ' creates cells w string values like "12.0 / 100%", "5.0 / -28.57%" etc.

The ".Text" method extracts the visible string from your NumValues and PctValues rather than the underlying numeric value. Just rewrite as a loop to do all the cells. For a very large range, you can speed up by creating NumAndPct values in a variant array and assigning the array to the target range.

If you are doing this with code in vba you would use the Format function for each item and append the cells in a similar fashion. If a simple formulaic version would work, something like this, substituting the sheet names in where needed.