Need to know what VBA function does this

aztecguitars

New Member
Joined
Dec 21, 2016
Messages
4
I'm new to VBA and don't know what search term to search for to find tutorials on what I want to do. Here it is:
I'd like to have VBA loop through numbers 1-100 and fill a particular cell in a spreadsheet that I made. I want it to wait for the calculations to process each time it fills in a new number (The sheet is huge and has a ton of different functions so takes about 10 seconds to process each time a change is made) and then save the output from another cell in the spreadsheet in a new row. I believe that VBA is able to do this, but I just can't find the correct terminology to search for in order to learn how to do it.

Thanks!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to the forum!

If I understand correctly, you want to sequentially fill a certain cell (let's say it's A1 for this example) with the numbers 1 to 100. A1 is a precedent cell for (many?) other formula cells on the same sheet that take approximately 10 seconds to calculate each time the value of A1 is changed. Once the sheet calculates you want to put the value of some other cell somewhere in the sheet as a record. If that is correct, try something like this:
Code:
Sub test()
Application.Calculation = xlCalculationManual
For i = 1 To 100
    [A1] = i     '<---change cell address to match your particular cell
    ActiveSheet.Calculate
    'add some code here to put the value of your other cell elsewhere in the sheet
Next i
Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
The first half of the script is working great, but I can't figure out how to get the data to paste into consecutive rows after the calculations run. I can get it to paste into a single cell, but what I'm trying to get it to do is to start at L3 and then paste the values from L3 to L102. I have tried multiple things, but with no luck. Can you help? Here's what I have so far:

Sub test()
Application.Calculation = xlCalculationManual
For i = 1 To 100
[I1] = i '<---change cell address to match your particular cell
ActiveSheet.Calculate
Range("I31").Copy Range("L3")

Next i


Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
I'm sure there are better ways to do this, but I got one method working:

Sub test()
Application.Calculation = xlCalculationManual
For i = 1 To 20
[I1] = i
ActiveSheet.Calculate
Range("I31").Copy
Range("P" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

Next i


Application.Calculation = xlCalculationAutomatic
End Sub



It works, but it's a little on the slow side. If anyone has suggestions to speed it up, please let me know. Thanks!
 
Upvote 0
I'm not sure why you turn calculations off when you want formulas to calculate? Turning off screenupdating is probably a better bet.

You can simply assign values without copy and paste:

Code:
Range("P" & Rows.Count).End(xlUp).Offset(1, 0).value2 = range("i31").value2
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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