rmccafferty
Board Regular
- Joined
- Jul 16, 2009
- Messages
- 60
First the goal. I have data that is downloaded into a spreadsheet. It comes in the form of a question and answer in text format which I then open in Excel. The lines read much like the two following lines.
Name: Robert
Volume: $10,000.00
Of course there are more entries with data of many types. I need to make the data more usable, then put it into a "table" and analyze various portions of it. that is, I need to analyze groups of data, or a series of these entries considered as a group.
Right now I am trying to get the downloads into a usable format.
The first thing I need to do is to separate the question portion of the string from the answer portion of the string. The obvious divider is the colon. So, with the entire string is in column A, I used the following formulas in columns C and D, which worked fine when manually entered, or entered once is the spreadsheet and copied down into additional rows within the same spreadsheet.
=IF(A38="","",LEFT(A38, SEARCH(":",A38,1))) in column C gets me the question portion of the string (everything through and including the colon). The first part of the if statement just checks for blank entries and returns a blank cell if the cell in column A is blank), and
=IF(A38="","",RIGHT(A38,LEN(A38)-SEARCH(":",A38,1))) gets me the answer portion of the string (what comes after the colon).
When I manually copy and paste these formulas (Ctrl-C and Ctrl-V) within the spreadsheet, it works fine. It also works fine is I use paste special with pasting formula or pasting values within the same spreadsheet.
Now comes my problem. I want to do this with a macro. That is, a lot of these Q&A "forms" are going to come in and I would like someone with no particular Excel experience to be able to run a macro that will paste these formulas into the appropriate cells. Further, this means that I must copy the formulas from one spreadsheet and paste them into a different spreadsheet. With a very strong preference of doing it with a macro.
So of course I hand entered my formulas into one spreadsheet and tested using a macro to copy them into a different spreadsheet. And that failed. Instead of copying over the formula, it copied the value of the formula in the originating spreadsheet. That is, it functioned exactly as if I had done a copy and paste special, selecting values in the paste special selection box.
When I tried to copy and use paste special in the different spreadsheet, paste special gave me a long list of options, but none of the familiar ones of formats, formulas, values, etc.
If it matters, the excel files are in the older XLS format.
So then I tried to paste the formula using a macro. I used code that included the following line to paste the formula into a cell:
'ActiveCell.FormulaR1C1 ="=IF(A38="","",LEFT(A38, SEARCH(":",A38,1)))"
It chooses the cell where I want to paste and then puts the formula within quote marks so it will be pasted as text into the cell.
But then I get a compile error, saying "Expected: line number of label or statement or end of statement" I obtained the first part of the syntax by recording a macro in which I pasted something and then using the Excel generated line of code for this line, changing the portion after the R1C1= section. For some reason does not like use text that becomes a formula.
What am I doing wrong?
My first choice would be to learn how to fix my code int he macro. But I would really love to know the answer to how to get around both versions of the problem.
Name: Robert
Volume: $10,000.00
Of course there are more entries with data of many types. I need to make the data more usable, then put it into a "table" and analyze various portions of it. that is, I need to analyze groups of data, or a series of these entries considered as a group.
Right now I am trying to get the downloads into a usable format.
The first thing I need to do is to separate the question portion of the string from the answer portion of the string. The obvious divider is the colon. So, with the entire string is in column A, I used the following formulas in columns C and D, which worked fine when manually entered, or entered once is the spreadsheet and copied down into additional rows within the same spreadsheet.
=IF(A38="","",LEFT(A38, SEARCH(":",A38,1))) in column C gets me the question portion of the string (everything through and including the colon). The first part of the if statement just checks for blank entries and returns a blank cell if the cell in column A is blank), and
=IF(A38="","",RIGHT(A38,LEN(A38)-SEARCH(":",A38,1))) gets me the answer portion of the string (what comes after the colon).
When I manually copy and paste these formulas (Ctrl-C and Ctrl-V) within the spreadsheet, it works fine. It also works fine is I use paste special with pasting formula or pasting values within the same spreadsheet.
Now comes my problem. I want to do this with a macro. That is, a lot of these Q&A "forms" are going to come in and I would like someone with no particular Excel experience to be able to run a macro that will paste these formulas into the appropriate cells. Further, this means that I must copy the formulas from one spreadsheet and paste them into a different spreadsheet. With a very strong preference of doing it with a macro.
So of course I hand entered my formulas into one spreadsheet and tested using a macro to copy them into a different spreadsheet. And that failed. Instead of copying over the formula, it copied the value of the formula in the originating spreadsheet. That is, it functioned exactly as if I had done a copy and paste special, selecting values in the paste special selection box.
When I tried to copy and use paste special in the different spreadsheet, paste special gave me a long list of options, but none of the familiar ones of formats, formulas, values, etc.
If it matters, the excel files are in the older XLS format.
So then I tried to paste the formula using a macro. I used code that included the following line to paste the formula into a cell:
'ActiveCell.FormulaR1C1 ="=IF(A38="","",LEFT(A38, SEARCH(":",A38,1)))"
It chooses the cell where I want to paste and then puts the formula within quote marks so it will be pasted as text into the cell.
But then I get a compile error, saying "Expected: line number of label or statement or end of statement" I obtained the first part of the syntax by recording a macro in which I pasted something and then using the Excel generated line of code for this line, changing the portion after the R1C1= section. For some reason does not like use text that becomes a formula.
What am I doing wrong?
My first choice would be to learn how to fix my code int he macro. But I would really love to know the answer to how to get around both versions of the problem.
