Formulating lots of cells in EXCEL 1

[SenorJones]

I have a lot of cells that I want to "formulate". That is I have lots of cells that have 3+4 for example and I want to change them to =3+4 giving the product 7 (of course). I want to do this on mass. Can this be done globally ??

 

[muschka]

You can use Find & Replace

Of the Edit menu select Find, Window that opens have Replace option.(Office 2000 & XP ; dont know previous versions)

Hope it helps

 

[SimonDavis]

I can see a problem with that - I would assume that in fact not all the cells actually contain '3+4' so it's going to be difficult to know what to replace.

Followng might help, but without more details of your sheet, I can't guarante it.

I think you'll probably need to use some sort of macro to work through the sheet. I don't think recording one will work as it will only record the action for the specific cell you're working on.

Is the sheet really all just a bunch of numbers as described? I ask this because you will need some way to plonk an = in front of the cell contents, but don't want to do that if there is text etc.

If it really is just numbers, you can paste the code below onto a button - it will stick the = in front of whatever is in the cell, then move down to the next one. But if the result is an error, the macro will crash - e.g. if one of the cells has just an = in it, the end result will be == which is incorrect syntax for excel.

Change the 10 to however many cells you want to replace at a time. It will only work downwards, but you can experiment with the offset numbers to change that:

For j = 1 To 10
Dim oldv, newv As Variant
ActiveCell.Select
oldv = ActiveCell.Value
newv = "=" + oldv
ActiveCell.Value = newv
ActiveCell.Offset(1, 0).Select
Next j

Suggest you work on a copy of your sheet! If you need help to make this work, post again.

 

[SenorJones]

Thanks Simon.

I'll try what you've recommended. As assumed I'm not just working with simple numbers but links with text that I have concatonated together from other data imputs. All are unique! Any recorded macro has previously remembered individual letters names hence taking away any ability to refresh with different data. So my original 3+4 converting to =3+4 was a simplification but the problem still stands.

I'll try your above and see how far I get.

Thanks all

Snr. Jones

 

[Store300]

If you have problems with the routine you can try amending:
line 5 to newv = "=" & oldv
line 6 to ActiveCell.Formula = newv