use of worksheetfunction in vba 1

[JoeyLoving]

I need help. I have .txt files sent to me routinely and I am trying to write vba to rename columns, rows, add calculated columns, page breaks, etc.
So far, I have been able to do all that but I am having trouble with worksheetfunctions that need to reference relative cells.
here's what I am trying to do.
1. select subtotal cell
2. sumif(b1:b1000),=r-1c0,(d:d1000)
column b contains the name of the companies i am trying to total
basically, I am trying to subtotal each page
I think I am mixing vba and excel too freely but I can't seem to get this working.
Since I am always working with new files, totaling in excel won't help.
Any ideas?

 

[SkipVought]

Hi,

"Since I am always working with new files, totaling in excel won't help."

Huh? You are running short on Excel's capabilities. Get out of VBA and back to the sheet.

Here's the drill.

Data > Import External Data > IMPORT to add an import query table to your sheet. From now on, unless your .txt filename changes, all you need do to get new data, is Data > REFRESH

Data > Data Range Properties - Check the box at the bottom to Fill down data in columns adjacent to data This will make your data-related formulas, DYNAMIC to the import row range.

Data > Subtotals... is where you can subtotal on the break or breaks in your data (as long as your SORT is proper) You can choose an option to PageBreak on each break (or not).

Depending on your requirements, you might need some VBA in that process, but your coding should be very _minimal.

Well, how's that, Joe?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeyLoving]

Sounds good but the data>import external data>import option is not available in excel 2007. Apparently there are quite a few differences from previous versions.
And, yes, the name of the files are different every time.
I like the idea of using import. Any idea how to get there in 2007?

 

[SkipVought]

But it is.

In the Data thing, tab, whatever its called, on the left you have FOUR, I believe I remember, query options, and I think the 4th is text.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ettienne]

Skip is right.
Click on Data, then From Text in the Get External Data area.

 

[JoeyLoving]

that's fine to get the data but how do I get the totals? the wizard only allows for column totals. obviously i am missing something.
I really appreciate the advice

 

[SkipVought]

"the wizard only allows for column totals"

You want ROW totals? Write a formula in adjacent column(s).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeyLoving]

Thanks!