Macro - Series of formulas in a loop to build tables

Kostas

can you please upload a sample workbook. Make things much easier to interpret.

Also, unless it is obvious from the samle workbook, give more detail on the text / non text entries in the columns.


rylo

Rylo,

Thank you very much for replying. I am attaching a sample workbook that will make things clearer.

The workbook has 4 sheets. "control" which includes just the buttons that run the macros, "statistics" where I want the calculations to be done, "formdata" & "formdata" which have columns of data.

Now, what I am doing is running one macro that opens some excel files and grabs data and pastes them in sheets "formdata2" & "formdata". What I want to do is to run a macro that does some calculations on these data and puts the results in the "statistics" sheet.

As you will see I have already created some tables in the "statistics" sheet in columns A,B, G,H. Imagine this sheet as split in two parts, I want the calculations for data coming from "formdata" on the A,B,C,D,E columns and the calculations for data coming from "formdata2" on the G,H,I,J,K columns.

The following columns do not change: A,B,G,H as they are just text/numbers which are standard (although it would be very nice to figure out a way to make the macro "write" them as I could use this trick in many other tools). I want the macro to fill in columns C,D,E with calculations performed on data coming from "Formdata", and columns I,J,K filled with calculations performed on data coming from "formdata2".

The formula's I am using is the Frequency for columns C,H, Sums for D,I and percentages for E,J as you will see from the macros that are included. So far I have pre-written all the variations of the macro that does this work from me, setting specifically where the results should be put. I want the macro to automatically place the results based on a standard distance from table to table (i.e put every table 5 rows under the other). The way the tables are placed does not need to be like I have put them.

I hope all this makes clearer what I want to do.

Thanks in advance for any time you spend on this one.

Regards,

K.

Hi

See how this goes. Put into a general module

Please Login or Register  to view this content.

rylo

Rylo,

This is pure genius! I am just an amateur in VBA but this seems a simple and elegant solution. I really thank you very much for taking the time and effort to help me on this and I will do my best to return the help to other members on this board (albeit not at the macro section i'm afraid, i'm a bit more capable in formulas etc).

Thank you very much, it works perfectly.

Kind regards,

Kostas