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

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

CodePudding user response：

why loop when you can simply paste special and divide.

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

CodePudding user response：

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