Can't get a simple multiply macro working

[Hyflex]

Sub Test()
Range("J:S").SpecialCells(xlCellTypeConstants, xlNumbers).Select
        With Selection
            .Cells.Value = .Cells.Value * 2.5
        End With
End Sub

I want All cells with numbers in to be multiplied by 2.5 but if its a blank or zero to ignore the cells

 

[jeffreybrown]

How about something like this...

Sub Test()
Dim rng As Range
Dim c As Range
    Set rng = Range("J:S")
    For Each c In rng.Cells
      If isnumeric(c.value) then
        c.Value = c.Value * 2.5
      end if
    Next
End Sub

 

[Michael M]

Try

Sub Test()
Range("J:S").SpecialCells(xlCellTypeConstants, xlNumbers).Select
        For Each Cell In Selection
            Cell.Value = Cell.Value * 2.5
        Next Cell
End Sub

 

[jeffreybrown]

A little update to mine...

It's may not be a good idea to do all of column J:S so using the LR variable would help narrow the scope of the cells the macro has to process.

Sub Test()
    Dim rng As Range
    Dim c As Range
    Dim LR As Long
    LR = Range("S" & Rows.Count).End(xlUp).Row
    Set rng = Range("S1:J" & LR)
    Application.ScreenUpdating = False
    For Each c In rng.Cells
        If c.Value <> "" Then
            c.Value = c.Value * 2.5
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[Hyflex]

Try

Sub Test()
Range("J:S").SpecialCells(xlCellTypeConstants, xlNumbers).Select
        For Each Cell In Selection
            Cell.Value = Cell.Value * 2.5
        Next Cell
End Sub

Both work wonderfully, thank you very very much

Cheers guys, this is much appreciated.

Note: jeffreybrown your second macro doesn't work :/ it says type mismatch...

 

[Michael M]

Glad you got it sorted.
Best option is Jeffreys.
It's not always a good idea to use Select.Selection when there are better options.

 

[Hyflex]

Glad you got it sorted.
Best option is Jeffreys.
It's not always a good idea to use Select.Selection when there are better options.

I understand what he is saying about that, we have about 500rows at the moment, but they are all different data sets so it might be 10rows then 4 rows of blank space, then 5 rows of data and then again back to 4 rows of blank space.

Both of your first macros take about 1min to complete. We have some other macros what take just as long (all macros together take about 8mins to complete)

 

[jeffreybrown]

I just ran the macro again and no error.

As far as the varying rows...no problem.

That is what the LR variable does. It looks for the last row no matter how many blank are interspersed throughout the dataset.

 

[Michael M]

Hmm, Jeffreys works fine for me in 2007

 

[Hyflex]

Hmm, Jeffreys works fine for me in 2007

I'm running 2007, maybe its because I Have text in places... so define with special cells?