Copy a formula from 1 spreadsheet to another, preferable with a macro

[managementOptions]

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.

 

[Gavona]

Please re-post in Forum707 since you want a VBA (macro) solution.

Another approach would be:
Data, TextToColumns with colon as a delimiter

When you post in Forum707 please clarify:
Is everything in a single column, A?
Do you need to capture the name of the source file or something else to distinguish the results from one file from that of another?

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.

Can you give us a bit more about this? How do you see the final data table being structured?

Gavin

 

[SkipVought]

hi,

The lines read much like the two following lines

Does that mean the you ALWAYS have 2 lines, where the row mod 2 of the QUESTION is 1 and the row mod 2 of the ANSWER is 0?
Does your data start in row 1?

Skip,

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

 

[managementOptions]

I need to take a number of steps after this one. This is the only part that is an issue.

I just want to copy the formulas into a spreadsheet via a macro. I cannot seem to make it work by just typing the text of the formula into the macro, as in "Macro Text to be entered" ...

Or by copying the formula from a different spreadsheet (while auto recording a macro) and then pasting into the new spreadsheet.

I tried all the options I could think of, as noted above, but cannot figure out how to do it. I have had the same problem in the past with other tasks. The same problem of copying a formula into a spreadsheet via a macro.

I am sure that I can find a workaround in this particular problem. But I would really, really like to be able to paste formulas into cells with a macro. Surely there is a way to do it.

 

[SkipVought]

Turn on your macro reporter,

COPY cell(s) containing the formula(s) of interest,

Select your other worksheet range

Edit > Paste Special -- FORMULAS

Turn off your macro recorder and observe your recorded code.

If you need help with the VBA code, please post your VBA-related questions in forum707.

Skip,

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

 

[Gavona]

Or, if you want the formula itself to be entered by the macro:
Turn on the recorder.
Edit a cell containing the working formula.
Turn off your macro recorder and observe your recorded code.



Gavin