Hello there.
Am in need of some assistance to create a macro that will be an amazing time saver.
I have built this sample file for the sake of simplicity. If anyone can assist me with this task on this spreadsheet then I can recode it to work in the real file.
The real file is a very hefty tool for testing financial data.
Here are the steps I need the macro to perform:
With the file open and calculation set to manual, I need the macro to:
1. Copy cells from worksheet !VARIABLES C23:33
2. Paste Special > Values into worksheet !TESTER C10:C20
3. Calculate (in the real file, this can take up to 20 seconds)
4. After calculation is complete, COPY from worksheet !TESTER C23:C33
5. Paste > Values into worksheet !RESULTS C23-C33
Then, it should LOOP back to step 1 and repeat, except that:
Step 1 Should copy the next column over inside the VARIABLES worksheet (ie D23:D33, E23:E33, and so on) every time this loops until it reaches the cell that says "END" in $V$10
Step 5 Should copy the next column over inside the RESULTS worksheet (ie D23:D33, E23:E33, and so on) every time until the macro ends because the condition in step 1 is met ("END" found in $V$10)
Steps 2-4 are exactly the same.
Again, this is a sample file and is purely for functionality.
I really appreciate any assistance I can find on this.
Thanks again!
William
Last edited by williampdx; 01-11-2012 at 05:43 PM. Reason: SOLVED
This should do it:
Option Explicit Sub RunTests() Dim TestRNG As Range, Tst As Range, Col As Long Set TestRNG = Sheets("Variables").Rows(10).SpecialCells(xlConstants, xlNumbers) Sheets("Results").Range("C23:EE33").Value = "" Col = 3 For Each Tst In TestRNG Sheets("Tester").Range("C10").Resize(11).Value = Tst.Resize(11).Value Sheets("Results").Cells(23, Col).Resize(11).Value = Sheets("Tester").Range("C23").Resize(11).Value Col = Col + 1 Next Tst Sheets("Tester").Range("C10").Resize(11).ClearContents End Sub
Last edited by JBeaucaire; 01-11-2012 at 10:56 AM. Reason: Correction, colored
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Let me be more clear.
I need it to copy the results from C23:C33 from !TESTER sheet so that when it is done the !RESULTS page would be populated with:
3 oranges
3 apples
2 grapes
etc....
As if now, it is just copying over #'s.
Other than that, the macro works swimmingly.
Can you help me to get this to work?
William
Last edited by williampdx; 01-11-2012 at 10:35 AM. Reason: mistake in range
Check the code above again, I made one tiny correction, it is highlighted for you. My apologies.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I tried the code above and it copies the correct range, but it is not calculating before each step.
It should be performing a calculation so that after calculating in !TESTER C23:C33, it moves the results of those calculation to the columns in !RESULTS C23:U33. It should look like:
see imageresults.jpg
Does that make sense?
William
Last edited by williampdx; 01-11-2012 at 11:30 AM.
Excel normally calculates on its own. Is your "calculation mode" set to manual?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
You are correct in that if I set the calculation to automatic, the macro works perfectly and does everything I need it to do.
But, on the file I am going to modify this macro to work on, I need excel to manually calculate.
How can we modify the macro to calculate at the appropriate times to give me the results I need with the file set to manual calculation?
Application.CalculateFull somewhere????
Thanks!
Sure, can you not take a guess where? Take a guess and insert it... let me know if you get it.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks to you, I have figured every piece of this out and have already adapted this to my other sheet.
I made adjustments for a manual calculation at what I think is the appropriate time for it.
Thanks again for all your help. This will save me hundreds of hours of work!Sub RunTests() Dim TestRNG As Range, Tst As Range, Col As Long Set TestRNG = Sheets("Variables").Rows(20).SpecialCells(xlConstants, xlNumbers) Sheets("Results").Range("C23:EE34").Value = "" Col = 3 For Each Tst In TestRNG Sheets("Tester").Range("C10").Resize(12).Value = Tst.Resize(12).Value Application.CalculateFull Sheets("Results").Cells(9, Col).Resize(12).Value = Sheets("Tester").Range("C31").Resize(12).Value Col = Col + 1 Next Tst Sheets("Tester").Range("C10").Resize(12).ClearContents End Sub
William
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks