VBA divide by 1000 without deleting formula

Question

I am currently using this basic code to divide the value of a cell by 1000:

Sub Divide_by_1000()
    Dim cell As Range
    For Each cell In Selection
        cell = cell / 1000
    Next
End Sub

Whilst it works very well for hard-coded numbers, if used on a cell with a formula in it, it removes the formula and outputs a number.

Ideally, I would like the macro to work as it does for a cell with a number in it, but for a cell with a formula I would like it to wrap brackets around the current formula and put a /1000 at the end (i.e. keeping the formula in tact)

I believe there will need to be a test to check if the cell has a formula in it first and apply the code I already have if it doesn't and the code I outlined above if it does.

Any help would be much appreciated.

Answer 1

You can check if the cell has a formula by checking if the first character is a equal sign =

If Left$(cell.Formula, 1) = "=" Then

or even better

If cell.HasFormula Then

and then rewrite the formula extended by ( … )/1000

cell.Formula = "=(" & Right$(cell.Formula, Len(cell.Formula) - 1) & ")/1000"

also I recommend to check if the cell.Value is a number before you divide by 1000

ElseIf IsNumeric(cell.Value) Then
   cell.Value = cell.Value / 1000

So you end up with something like

If Left$(cell.Formula, 1) = "=" Then
    cell.Formula = "=(" & Right$(cell.Formula, Len(cell.Formula) - 1) & ")/1000"
ElseIf IsNumeric(cell.Value) Then
    cell.Value = cell.Value / 1000
End If

Note while this will work for normal formulas, it will crush eg array formulas.