I've been wracking my brain trying to piece together a UDF to save me a ton of time, but I'm having difficulty.
So here's the situation:
- I have several comma delineated .txt files (10-20 or so) that are imported into an Excel file.
- Each .txt file gets its own Sheet in the Excel workbook.
- The first sheet in the workbook is a table that references all of the other sheets to make various calculations.
- Each sheet of raw data has varying rows and columns. Columns vary from 4-20 and rows from 10-1000.
- The first row in each sheet is a column header from the imported .txt file and every sheet has a column called "Acres".
- What I want to do is search a sheet for the column "acres" and then sum that column.
So my thought was to create a UDF called SumColumn, that takes the sheetname and desired column name as arguments.
Something like:
Function SumColumn(SheetName As String, ColName As String)
*Search Row 1 of SheetName and find the word ColName and return column*
*Sum corresponding column*
End
I did a lot of googling and looking at various code snippets trying to figure out a way to do this seemingly simple operation, but I'm having no luck. I'm having trouble with the syntax of returning the column value as a letter, not a number (which I assume you need to later sum the column). I'm also having trouble with the syntax of referencing other sheets, with the sheet name being a variable. Should it be declared as a string initially or what?
Currently I just manually find the column named acres and do =SUM(SheetA!E:E) or =SUM(SheetB!G:G) and so on. That requires me to look at each sheet and manually enter the column, but it's possible the columns could change on a given sheet, so checking for the column name would be best.
Any help would be appreciated. Thanks!
So here's the situation:
- I have several comma delineated .txt files (10-20 or so) that are imported into an Excel file.
- Each .txt file gets its own Sheet in the Excel workbook.
- The first sheet in the workbook is a table that references all of the other sheets to make various calculations.
- Each sheet of raw data has varying rows and columns. Columns vary from 4-20 and rows from 10-1000.
- The first row in each sheet is a column header from the imported .txt file and every sheet has a column called "Acres".
- What I want to do is search a sheet for the column "acres" and then sum that column.
So my thought was to create a UDF called SumColumn, that takes the sheetname and desired column name as arguments.
Something like:
Function SumColumn(SheetName As String, ColName As String)
*Search Row 1 of SheetName and find the word ColName and return column*
*Sum corresponding column*
End
I did a lot of googling and looking at various code snippets trying to figure out a way to do this seemingly simple operation, but I'm having no luck. I'm having trouble with the syntax of returning the column value as a letter, not a number (which I assume you need to later sum the column). I'm also having trouble with the syntax of referencing other sheets, with the sheet name being a variable. Should it be declared as a string initially or what?
Currently I just manually find the column named acres and do =SUM(SheetA!E:E) or =SUM(SheetB!G:G) and so on. That requires me to look at each sheet and manually enter the column, but it's possible the columns could change on a given sheet, so checking for the column name would be best.
Any help would be appreciated. Thanks!
