EXCEL: Add /1000 to multiple formulas on a sheet

[neronikita]

Hello all!

I have an interesting question. I have a user who has a spreadsheet with various formulas on it. It's huge - many rows and columns. He wants to be able to run a macro that will find all cells with any formula in it and add /1000 to the end of the formula without changing the rest of the formula. Is this possible?

Thanks!

Di

 

[lestatdelioncourt]

if you create a macro with the following code it should produce the required results

Dim col, cur_col As Double
Dim row, cur_row As Double

Range("a1&quot.Select

' find last column
Selection.End(xlToRight).Select
col = ActiveCell.Column

' find last row
Selection.End(xlDown).Select
row = ActiveCell.row


For Each c In ActiveSheet.Range(Cells(1, 1), Cells(col, row))

If Format(c.Value, &quot;&quot <> c.Formula Then
c.FormulaR1C1 = Left(c.FormulaR1C1, 1) + &quot;(&quot; + Mid(c.FormulaR1C1, 2, Len(c.FormulaR1C1) - 1) + &quot/1000&quot;
End If
Next c

 

[lestatdelioncourt]

sorry the last bit of code misses the last row. try this

Dim col, cur_col As Double
Dim row, cur_row As Double

Range(&quot;a1&quot.Select

' find last column
Selection.End(xlToRight).Select
col = ActiveCell.Column

' find last row
Selection.End(xlDown).Select
row = ActiveCell.row


For Each c In ActiveSheet.Range(Cells(0, 0), Cells(col + 1, row + 1))

If Format(c.Value, &quot;&quot <> c.Formula Then
c.FormulaR1C1 = Left(c.FormulaR1C1, 1) + &quot;(&quot; + Mid(c.FormulaR1C1, 2, Len(c.FormulaR1C1) - 1) + &quot/1000&quot;
End If
Next c

 

[PeterMoran]

Hi,

Having done a similar exercise recently can I suggest a couple of areas where caution is required with the excellent code lestatdelioncourt has proposed:

1. You may not want to change any formulas which are SUMs as that will upset totalling - also consider SUBTOTALs if they are present.

2. It may not make much sense to do this with text formulas if they are present.

It might almost be advisable on a large spreadsheet to have the updating done like a search and replace where there is an option on each formula as to whether to update or not.

Good Luck!

Peter Moran
Two heads are always better than one!!

 

[neronikita]

Thank you both! I just got back to work - I will try this!

Di

 

[GlennUK]

Can I ask a question Di (neronikita) ... is the reason for wanting to /1000 because of display requirements only?

If so, a custom format would do the trick. ( any number format followed by a , )

If not, then sorry I asked.

Glenn.