Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - Multiplying every cell within a range by a value

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This will do the whole at once:

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

Before:

enter image description here

After:

enter image description here

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.

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...