Add equals in front of a string

[dgale19]

I use Excel 07 with Windows XP. I need to display an equation in a cell and have it just show the total. Fox example, the cell with show 100,000 and when you click on the cell you will see =25+50+10+15. What I am doing now is a get updated figures in pdf format every day with around 100 items so I copy and paste the data into notepad and then open it in Excel. My first sheet gets the data in order. Then on the next sheet I organize the data into 10 categories. Let's say that I have in column A, rows 1-4, I have 25, 50, 10, and 15. Then in cell B1, I have a formula, =A1+A2+A3+A4. Then under that in column B2 I have a macro that copy cell A1 and pastes it special as values. The macro does this for all 10 of my categories. Some categories have 4 values, some have 10. So in cell B2, I would have 25+50+10+5 but there will be know equals in the front. As of now, I just go through all the categories and put an equals sign in front of all the strings. Then I copy the strings and paste special as formulas into the columns of my report that I give to my managers each day. They want to be able to see the values in the cell when they look. Does this make sense? Can I have a macro that adds an equals in front of each string? The problem I have when I tried to do the macro to add the equals is the next day when the macro runs, the formula ends up having yesterday's values instead of the new values from this day.

 

[Richard Schollar]

Hi

Not sure I completely understand what you require but:

'cell A1 contains:   5+10+20
Range("A1").Value = "'=" & Range("A1").Value
'will produce in cell:   =5+10+20 as a string (not as a result)
'subsequently:
Range("A1").Formula = Range("A1").Formula
'should turn the A1 cell into a genuine formula ie showing 35

Note that there is an apostrophe ' preceding the equals in the first non-comment code line.

 

[dgale19]

I just record macros. I don't know how to do code. Is there an easy way to do that without code?

 

[Richard Schollar]

Yep - when you type the formula in (eg =25+10+5), just precede it with an apostrophe - s0 what you type in becomes:

'=25+10+5

 

[dgale19]

That doesn't work for me because I don't type =25+10+5. I actually type =A1&"+"&A2&"+"&A3. Someone did that for me, I'm not sure why. I will get 25+10+5 into a cell when I copy and paste special Values from that formula. The numbers change every day so it makes it harder. Maybe your code will work, how would I do that, do you think that is the best way? Do I use the Visual Basic Editor?

 

[Richard Schollar]

I'm sorry, I'm thoroughly confused as to what you actually want to do now. You already have

25+10+5

in the cell - is this not what you want? Do you want the result (ie 40) instead?

 

[dgale19]

yes I want the result 40 to show and when you click on the cell to view what is in it, you will see =25+10+5. But the result and numbers change every day so I need a solution that works for any amount of numbers. Maybe I should try to show you the actual worksheet

 

[Richard Schollar]

You can kind of do this using VBA (you would trigger some code that would evaluate the formula in the cell when the cell was clicked on and then you could probably write the original formula (=5+10+25) back into the cell once it was unselected. However, this seems to me to be an awful lot of effort with very little gain. Why don't you just place the individual values in separate cells and then sum those cells up? That's effectively what spreadsheets were invented for. Then you would have the individual values and the sum total?