Multiply a range of cells by a constant VBA

August 10, 2022

I tried to use the following code to multiply a range of cells by a constant, but it keeps giving me type mismatched. Can someone help please?

Sub Multiply()
 Dim rng As Range
 Dim myVal As Range
 
 Set rng = Range("B2:DR82")
 For Each myVal In rng
   myVal = Round(myVal.Value * 0.64, 2)
 Next myVal
End Sub

>Solution :

Check the value before multiplying:


Sub Multiply()
 Dim rng As Range
 Dim myVal As Range
 
 Set rng = Range("B2:DR82")
 For Each myVal In rng
    If IsNumeric(myVal.Value) Then
        myVal.Value = Round(myVal.Value * 0.64, 2)
    End If
 Next myVal
End Sub

But it would be faster, writing the range to an array, doing the maths on the array and then write back to the sheet.