Noob To R1C1 format, New Formula Received and I Need Help Please.

Hi There,

I am not so new to VBA in Excel but I will profess to be a noob when it comes to the R1C1 format in formulas.

The following lines have me stumped, because they only get part of the data I need. Can someone possibly interpret them for me please and let me know how to increase the amount of data each one takes from the origination locatio? I would be eternally grateful, as I can then tell the Division Chief that I was able, with some assistance from my friends, to successfully modify the macro for our personal use. Thank you to all who respond. Here is the code that has me cornfused

Please Login or Register  to view this content.

So, those are the lines that have been giving me fits since last week. I've tried debug.print statements, but just can't seem to figger this one out and was hoping one of ya'll could help.

Thanks again.

Hopelessly lost in Alabama

Alan,

Thank you for the response. Is there a way in VBA using the immediate window, to change formula's from the R1C1 format, to the other format where you specify the cell like A1, or C10?

I thought I heard or read somewhere that you could do that, but I cannot remember, nor can I find it searching on the net....but I'm not an expert in searching either....

Thanks again.

Derek in Alabama

Well, they work, sort of...yes, searching for the character "*" because it identifies a certain type of record in the raw, exported data. Depending on how many "*" there are, tells you the type of data. But, they aren't picking up the entire field in exported, raw data.

This excel macro file supposedly takes a text file and massages the raw data, which was exported from our financial system (an SAP ERP), then the macro saves the raw data, after massage, as an Excel file. Which is then run thru an MS Access macro, which further massages the data, (picking up some detailed information by matching fields in tables already set up in Access).

Then, at the last, there is a pivot table excel file, that is supposed to show you your data in whatever manner you wish, depending on how you set up the pivot table. The Sum fields are already set, the end user just brings down the data fields that uniquely identify the summed amounts.

Not that it matters but I work as a Financial Analyst for the Government, so that should tell you the importance of what I am trying to do, and I am not trying to influence anyone, was just stating my position and why I am asking these questions.

We were given these tools by a vendor at a conference and I am, (since I am the only Finanacial Analyst where I work that used to be a Software Engineer, but its been a long time since I have done any programming), tasked with seeing if these tools can help us in reporting the Status of the Funds allocated to us by Congress.

The two lines in question are part of a macro:

Please Login or Register  to view this content.

No, I don't expect anyone to plow through that listing, nor make any changes to it, I am just trying to understand why those lines are not getting all of the data in the field. Since this is the first macro that hits the raw data, it has to happen here, because it is present before this macro, and gone afterwards...

Thanks.

Thanks anyways people, I do appreciate all input. However, still confused, but will try to muddle through this on my own....I just can't see where those data fields are truncated in the transition between the raw text file and the new excel spreadsheett....