Using VBA to calculate %'s when range will vary

**Jessica.Bush** · 08-14-2011, 12:38 PM

Hi there,

I wonder if someone could help me with a problem which seems easy but yet I cannot think of a way to do it please?!

I have two tables, one has core hard values in it and the same table above. In the table above I need to add formulas (or calculate the values in VBA) based on the data immediately below it. Attached is an example workbook with formulas to show what I need. This should be very easy and normally I would use R1C1 formulas. What makes it more complicated is that the number of columns varies (A, B, C etc) AND the number of rows varies. I can define these as variables but struggling to use R1C1 formulas with this and yet struggling to write it so it is calculated inside the VBA.

Can anyone help me with this please? I hope this makes sense!

Many thanks.

**MarvinP** · 08-14-2011, 01:02 PM

Hi Jessica,

Pivot Tables allow showing data as a percent of column totals. Look at my attached to see if it doesn't solve your problem without needing VBA.

**Jessica.Bush** · 08-14-2011, 01:37 PM

Hi Marvin,

Thanks for your reply - I did a pivot to get the results in the first place but wanted a total of all minus the clients product (which changes each time) which is why I didn't do the % in the table.
Additionally I want the columns to be in order set out in the table - I want it to have the clients product and then an average and then the remaining items in any order. I can write pivot tables in VBA but don't know how to assign the order in this way.
Finally I want to have this in the VBA code as it is one of many steps that I am trying to automate so I don't want the user to have to stop and do it manually.

If the easiest way is to use a pivot then that is great but could you tell me how you would write code so that it gives (for example) whatever name is referenced in cell(1,1) first, then the name 'average' second from left and then the rest please? I can set the pivot up but not complete this final step.

Thank you so much for your help and advice!

**MarvinP** · 08-14-2011, 02:11 PM

Hi Jessica,

The first suggestion is to have the pivot come from a named range. If you insert rows into that space the pivot will grow or shrink to the size of the named range. You might create the pivot and then do VBA to define the named range to make it easy.

I believe the order will stay the same if you adjust it the first time. I really need an example to see what order you want and how it changes. You can drag pivot rows or columns up or across to place them in any order you want. I think they will stay like that with new data.

**Jessica.Bush** · 08-14-2011, 02:37 PM

Hi Marvin,

Is it not possible to do this using formulas then?

Can you show me on the example posted how to create a pivot table so the order is C first on the left, followed by D and then the rest please? This can answer my question.

This is all part of a much bigger load of code and is in the middle hence it all needs to be done via VBA and nothing manual (so cannot do the normal dragging, need to do the equivalent of this using code).

Thanks.

**MarvinP** · 08-14-2011, 02:56 PM

Hi,
Look http://www.gcflearnfree.org/excel2007/18.4
or simply click in a column heading and then move the cursor so it selects the side of the cell, click and drag it to where you want.

My experience has lead me to write a lot less code and use more and more of the standard features of Excel. It is, of course, to write code to do all this but it then becomes less useful when things change.

Using the example Pivot I supplied above. I turned on the macro recorder and drug column C to the front and then column D. The code generated was:

Sub Macro1()

    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of C"). _
        Position = 1
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of D"). _
        Position = 2
End Sub

Recording macros and then seeing what I did in VBA is my method for learning.

**barryleajo** · 08-15-2011, 10:22 AM

Hi Jessica
Try the attched for a VBA solution.
I have written the code to output the values two columns to the right of the (lower) data table, and in line with your expected solution - tweak as required.
This means this output table can move as the data table expands or contracts.
For testing purposes be sure therefore to delete the previous 'output table' before re-running the macro if the data table has changed size.
I guess I could have written a ClearContents bit but just don' t have the time today.
Run macro "calcpercent"
Barry