excel - Multiplying every cell within a range by a value

Question

I would like that the value of every cell within a range is multiplied by a specific value (let's say 4/7). I have this code, but I'm getting a "Type mismatch error".

Range("B1:B4").Value = Range("B1:B4").Value * 4 / 7

Any thoughts?

Answer 1

This will do the whole at once:

Range("B1:B4").Value = ActiveSheet.Evaluate("INDEX(B1:B4 * 4 / 7,)")

Before:

After:

Just to show. I ran this:

Sub foo()
Dim t
t = Now()
[B:B] = ActiveSheet.Evaluate("INDEX(row(B:B)*1,)")
[B:B] = ActiveSheet.Evaluate("INDEX(B:B * 4 / 7,)")
Dim x

Debug.Print (Now() - t) * 24 * 60 * 60
End Sub

Which fills the entire column B with the row number of the cell, then does the math that is required.

It took 2 seconds to do the entire 1 million + rows.