0

I am trying to divide all the cells in a selection by 1000 but not cells which have a sum formula.

Criteria:

  1. Cell must have a number.
  2. Cell should not contain the Sum() formula (any other formula is okay).

If both of these criteria are met then divide the cell value by 1000.

For Each cell In Selection.Cells
    If IsNumeric(cell) = False Or cell.Address = Left(ActiveCell.Formula, 5) = "=Sum(" Or cell.Address = Left(ActiveCell.Formula, 6) = "=+SUM(" Or cell.Address = Left(ActiveCell.Formula, 6) = "=-SUM(" Then
        MsgBox ("Selection either does contain numbers or has only sum formulae")
    Else
        cell.value = cell.value / 1000
    End If
Next
double-beep
  • 5,031
  • 17
  • 33
  • 41
VJos
  • 1
  • 1

1 Answers1

1

Not sure what you're trying to achieve with

Cell.Address = Left(ActiveCell.Formula,5) = "=Sum(

You can only compare one value with another.

I'm sure the more advanced people here will frown on the way I phrased the if statement but it does the job:

Sub divideBy1000()
    Dim formCheck As Variant
    For Each ccell In Selection.Cells
        Debug.Print (ccell.Formula)
        Debug.Print (ccell.Value)
        formCheck = InStr(ccell.Formula, "SUM(") 'This checks if SUM is in the formula and returns a value bigger than 0 if so (where it occurs in the string).
        If len(trim(ccell.value2))>0 Then
            If IsNumeric(ccell.Value) And (formCheck = Null Or formCheck = 0) Then
            'You have to use AND so none of the SUM formulas get through, no else needed this way
                If Left$(ccell.Formula, 1) = "=" Then 'EDIT to keep your formula
                    ccell.Formula = "=(" & Right$(ccell.Formula, Len(ccell.Formula) - 1) & ")/1000"                    
                Else
                    ccell.Value = ccell.Value / 1000
                End If
            End If
        End If
    Next ccell
End Sub

Edit: Added a catch for empty cells so they don't turn to 0 and added Instr to check for the SUM as suggested by @Marcucciboy2

Notus_Panda
  • 1,402
  • 1
  • 3
  • 12
  • You could make it more legible using `InStr()` but it wouldn't necessarily improve the function – Marcucciboy2 Nov 09 '22 at 14:04
  • Been checking with you suggestion and while it did what it should, I probably also should add Len(ccell) > 0 since I noticed empty cells getting through IsNumeric() – Notus_Panda Nov 09 '22 at 14:48
  • yeah i'd put it above the initial if `If len(trim(ccell.value2))>0 Then ` – Marcucciboy2 Nov 09 '22 at 14:53
  • Alright, cheers, adjusted as needed. Quick question though, is there a specific reason why you opted for the value2? – Notus_Panda Nov 09 '22 at 15:00
  • 1
    It can be slower https://stackoverflow.com/a/17363466/2727437 – Marcucciboy2 Nov 09 '22 at 15:03
  • 1
    Thanks for helping me learn as well :) – Notus_Panda Nov 09 '22 at 15:37
  • Hi Notus_Panda, thanks for the answer. It works well. I made a small change instead of "SUM" I made it "SUM(" because I want the macro to divide the cells which contain "SUMIF" formula. But now since it works I realized there is another problem, I want the macro to divide the cell value by 1000 but keep the existing formula in place. E.g. If cell has "Sumif(xx,yy,zz)" then it should do "(Sumif(xx,yy,zz)/1000) – VJos Nov 10 '22 at 14:02
  • This should be able to help: https://stackoverflow.com/questions/53481104/vba-divide-by-1000-without-deleting-formula – Notus_Panda Nov 10 '22 at 14:56
  • Thank you so much Notus_Panda and also Marcucciboy2!!!!! :) :) – VJos Nov 11 '22 at 08:33