CODEWITHSUNDEEP
excelvba
karan arora
karan arora

Reputation: 41

Divide a range of cells with a particular number if cell values are numeric

I have to divide all the cell values of sheet -"Databook" with a number 1000000 only if the cell contains numeric value. It means I have to divide only those cells in the sheet which contains numbers like 17577.2 , 2123, 13979123.22, 239812098321.1, and 9798.

Sub i()

'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A:Z")
For Each myVal In rng

If IsNumeric(myVal) = True Then

myVal = myVal.Value / 1000000

Else

Next myVal

End Sub

Upvotes: 2

Views: 279

Answers (1)

JvdV
JvdV

Reputation: 75900

You currently suffer from a missing End If, thus your current code won't run at all. Properly indenting your code would have revealed the issue. I would also recommend not trying to loop all cells in your current range. Note that these are (for Excel 2019 at least) 27.262.976 cells to go through (you might want to first find your range of interest first; last used row, last used column). This many calls will be terribly slow. Limit that numbers by just using the actual numeric values at least.

Try to uitilize SpecialCells. The way it works > <YourRange>.SpecialCells(XlCellType, [XlSpecialCellsValue]). For example:

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")

For Each cl In ws.Range("A:Z").SpecialCells(2, 1)
    cl.Value = cl.Value / 1000000
Next

End Sub

Where: .SpecialCells(2, 1) can also be read as .SpecialCells(xlCellTypeConstants, xlNumbers)

Note: If you have big chunks of cells that contain numeric values it might be beneficial to loop Areas property instead of Cells since you can load these into an array and perform calculations in memory before pasting back these values. This could also save you some valuable runtime.

Upvotes: 1

Related Questions