Need help converting spreadsheet operations into VBA

[LokiMundane]

Hello All,

I have a spreadsheet built to handle certain calculations from data that is input. To reduce the amount of manual effort needed in using the workbook and to make it more flexible for future use I'd like to convert it into a vba enabled worksheet.

My hope is that you smart people can help me identify a starting point in making this a VBA enabled workbook. I am happy to do the legwork but I don't even know where to begin.

I do have some experience with VB but none with VBA. There are several worksheets involved. I will lay them out and describe their function. I will then describe my ideal situation in the hopes that someone will be able to tell me "yes, this is possible and you should start by doing X,Y,and Z"

Worksheets involved

• UI Page
• Square Footage Page
• PN Counter Basement though 10th ( 11 pages total )
• PN Summary
• Data Table
• Weighted Table
• Summary Table
• Raw data pages ( 9 pages total )

Description of the function of each

UI Page

The UE page has a table with 10 floors and a place to input the square footage for each floor

Square Footage Page

This has a list of buildings and the square footage for each floor

PN Counter

This uses countif to look at the number of time each particular integer occurs in the raw data pages and returns a value. Then it returns the number with the highest occurrence, 2nd highest occurrence, 3rd highest occurrence. There is one worksheet for each floor currently.

PN Summary

This gathers data from all of the PN counter sheets and gathers the highest occurrence of a particular integer,2nd highest, 3rd highest

Data Table

This reads through raw data pages and reads values defined by countif and inputs them into a table. This is done per floor

Weighted Table

This reads through the numbers gathered by the data table and weighs them against the square footage input on the UI page to get a weighted average.

Summary Table

Infor from the summary table is pulled from the weighted table. The summary table also pulls data from PN summary

Raw Data Pages

There are currently 9 worksheets. They are laid out so that info has to be put in the following format

A | B | C | D | E | F |G | H | J |

a = basement
b = value 1
c = value 2
d = value 3
e = 1st floor
f = value 1
g = value 2
h = value 3

Etc.. All the way up to floor 10.

Ideally. I would like for the user to have to manually input buildings and square footages once. Then the building is chosen from a drop down list on the UI page. The UI page has a button that executes commands. When the button is clicked the program looks at how many floors are used prompts for a .csv file location. The .csv will contain all the info as laid out previously where A=floor b=value 1 C= value 2 D= value3 But the A column will have all floors ( for example 132 occurrences of basement , 400 occurrences of 1st Floor, and 432 occurrences of 2nd Floor ) The program would parse these and lay them out into a newly created worksheet ( probably better than putting them into an array as there could be 4000 plus values )


Is this possible? Currently my raw output format is a.csv file that I have to manually cut and paste into the worksheet. I have to do this process for each floor and each raw data page. On a lot of floors it can take me over an hour. When I have hundreds of buildings I need to do this with it can be time consuming.

Thanks for any advise or direction!!

[aydeegee]

Hi LokiMundane

What you describe seems do-able. Is it possible to load a workbook with some sample data, so we can see how your notes will fit into the workbook?

If you could ensure that it is obvious what the end result should be - that will help!

Regards
Alastair