CODEWITHSUNDEEP
excelvba
abigidiot
abigidiot

Reputation: 11

How to divide every cell in a column by a constant in VBA?

I am processing a data set that has about 50 columns, and the same columns are always off by a factor of ten. So, I just want to hardcode the specific columns (starting with F here) and divide every cell in the column by 10. Right now I am getting a "Type Mismatch" error. The code is below:

Sub FixData()
Dim finalRow As Long
Dim rng As Range
Dim cell As Range

finalRow = Range("A100000").End(xlUp).Row

 Set rng = Range("F1:F" & finalRow)
 
 For Each cell In rng
 cell.Value = cell.Value / 10
 Next cell
 
 
  
    
End Sub

Upvotes: 1

Views: 987

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

test if cell is an error and then test if it is a number prior to dividing:

Sub FixData()
Dim finalRow As Long
Dim rng As Range
Dim cell As Range

finalRow = Range("A100000").End(xlUp).Row

Set rng = Range("F1:F" & finalRow)
 
For Each cell In rng
    If Not IsError(Cell) Then
        If IsNumeric(cell) and cell <> "" Then
            cell.Value = cDbl(cell.Value) / 10
        End If
    End If
Next cell
End Sub

Upvotes: 1

Rosetta
Rosetta

Reputation: 2725

why loop when you can simply paste special and divide.

enter image description here

errors within the cells are ignored.

in vba, here is the code

Range("G10").Copy
Range("B2:E8").PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
Application.CutCopyMode = False

Upvotes: 4

Related Questions