Efficient multiplication in VBA with macros

[purinqui]

Hello friends, I am trying to create a code that is as efficient as possible.

What I need to do is Multiply a range of cells by a fixed value and then replace the value that each cell had with that new value.

For example:

In the "D5: 25" range I have different numerical values in each of the cells. Then I multiply each cell by a fixed value and that new value is replaced in each cell (I mean in the range "D5: 25").

I was able to solve it through two ways, but both are "inefficient", since when I run the code it shows that it takes a long time to perform all the multiplication in that range.

And also as I then need to do the same in other ranges too, I know that this code will not be efficient because it will take a long time.

I pass the two codes that do work but are inefficient.

Code 1:

Sub dolartopesos()
'Routine to pass to pesos all the prices of the products of each supplier that are in dollars.

dollars = Worksheets(3).Range("R1")

'---------------------------------------------------

With Worksheets(3)
'Currency exchange

For i = 5 To 25

.Cells(i, 4) = .Cells(i, 4) * dollars

Next i


End With


'---------------------------------------------------

End Sub

Code2:

Sub dolartopesos()
'Routine to pass to pesos all the prices of the products of each supplier that are in dollars.

dollars = Worksheets(3).Range("R1")

'---------------------------------------------------
''Currency exchange

Dim rng As Range: Set rng = Worksheets(3).Range("D5:25")
Dim cel As Range
For Each cel In rng.Cells
With cel
cel = Application.WorksheetFunction.Product(cel, dollars)
End With
Next cel

'---------------------------------------------------
End Sub


Both codes take too long to multiply in that range.

What could I do to speed up the multiplication?

Any contribution will be welcome. From already thank you very much.

Sebastian.

 

[footoo]

[D5:D25] = Evaluate("D5:D25*" & dollars)

Or :

[D5:D25] = [D5:D25*R1]

 

[Rick Rothstein]

Give this a try (change the highlighted ranges to match your actual required range)...

Sub DollarsToPesos()
  With Worksheets(3)
    .Range("D5:D25") = Evaluate(Replace("@R1*@D5:D25", "@", "'" & .Name & "'!"))
  End With
End Sub

 

[purinqui]

Or :

[D5:D25] = [D5:D25*R1]

This code works properly and fast.

Thank you very much.!

 

[purinqui]

Give this a try (change the highlighted ranges to match your actual required range)...

Sub DollarsToPesos()
  With Worksheets(3)
    .Range("D5:D25") = Evaluate(Replace("@R1*@D5:D25", "@", "'" & .Name & "'!"))
  End With
End Sub

This code works properly and so fast too !!.

Question: What is the reason to include these symbols : "@" and "'" & .Name & "'!"

Thank you very much.!

 

[Rick Rothstein]

The @ sign is simply a stand in character that gets replaced by the worksheet name (properly encased in apostrophe characters and followed by an exclamation mark) so that the ranges in the formula end up with a properly referenced sheet name. Just so you know, the code I posted is the same as what footoo posted... his code uses shorthand notation that requires the ranges to be fixed whereas my code includes the worksheet reference in case you need to run the macro with a different worksheet active plus my code can easily be modified to make the references dynamic (such as letting the code determine the last cell rather than hard-coding it) should that be required in the future.

 

[Rick Rothstein]

This question was asked and answered over in the "Excel Forum" forum...

Efficient multiplication in VBA with macros [SOLVED]

 

[purinqui]

The @ sign is simply a stand in character that gets replaced by the worksheet name (properly encased in apostrophe characters and followed by an exclamation mark) so that the ranges in the formula end up with a properly referenced sheet name. Just so you know, the code I posted is the same as what footoo posted... his code uses shorthand notation that requires the ranges to be fixed whereas my code includes the worksheet reference in case you need to run the macro with a different worksheet active plus my code can easily be modified to make the references dynamic (such as letting the code determine the last cell rather than hard-coding it) should that be required in the future.

Oh is fantastic this explanation.
Thank you very much for your time.
I appreciate the help you gave me .