apply a formula to an large range

JustinWWolcott

Board Regular
Joined
Mar 20, 2007
Messages
158
sorry if its been posted before, but I'm not really sure how to splice a query. Anyhoo, if I wanted to divide every number between D24 to H70 by Pi, could you do it without using a loop?

I can accomplish what I want using a loop, but there's a little lag that I want to clean up.

thanks for any help,
respectfully,
-Justin
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Justin,

Try the Evaluate Method, e.g.,

Code:
Sub bar()
Dim varArr() As Variant
Dim i As Long, j As Long
Let varArr = [Sheet1!D24:H70/Pi()]
'Lets have a peek
For i = LBound(varArr, 1) To UBound(varArr, 1)
    For j = LBound(varArr, 2) To UBound(varArr, 2)
        Debug.Print varArr(i, j)
    Next
Next
End Sub
:)
 
Upvote 0
You're welcome. :)

Also note, you can pass this array directly to your worksheet, e.g.,

Code:
Sub baz()
[Sheet1!D24:H70] = [Sheet1!D24:H70/Pi()]
End Sub
Please call me Nate, mate. Cripes, I just looked around for my old man! :eek: :LOL:
 
Upvote 0
Justin,

If you want to do the calculation in another range, say J24:N70:


Copy the following code into a Module.

Code:
Sub DivideByPi()
'
' Macro created 05/21/2007 by Stanley D. Grom, Jr.
' apply a formula to an large range - JustinWWolcott
'
' Thank you Jindon
'
    With Sheets("Sheet1").Range("J24:N70")
         .Formula = "=RC[-6]/Pi()"
    End With

End Sub


Then run the 'DivideByPi' macro.


Have a great day,
Stan
 
Upvote 0

Forum statistics

Threads
1,215,139
Messages
6,123,259
Members
449,093
Latest member
Vincent Khandagale

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top