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.