Copying answers from previous calculation

[rzgixxer]

Hello,

I'm trying to add a VBA code into a tool I'm creating for my thesis since the calculation is a repeatable calculation, although a lot of work to do by hand.
Perhaps one of you guys is able to help me out on this.

In my main worksheet "Resistance", I added a form control button where the macro is supposed to be assigned to.
What it should do...

1. It must select cell D7 in another worksheet called "Sav. paper" (this cell contains is a speed value, upcoming calculations are based on this value);
2. Cell D7 should start at a value of 10;
3. In cells B50:H50 the answers are given for the calculations with the speed of cell D7.
4. These answers need to be pasted as values into cells I50:O50

5. Select cell D7 and set the value of 10 to a value of 10.5 (speed is in steps of 0.5)
6. Answers given to this speed value end up in cells B51:H51
7. These answers need to be pasted as values into cells I51:O51

8. Select cell D7 and set the value of 10.5 to a value of 11
9. Answers given to this speed value end up in cells B52:H52
10. These answers need to be pasted as values into cells I52:O52

And so on until cell D7 has reached a speed value of 55...

It seems quite simple, but I'm unable to fix this code by myself..
If there are suggestions to even do this quicker, all are welcome!

Thanks already!

 

[JackDanIce]

Welcome to the board. Try:

Sub Macro1()

    Dim x       As Double
    Dim r       As Long
    
    Application.ScreenUpdating = False
    r = 50
    
    With Sheets("Sav. paper")
        For x = 10 To 55 Step 0.5
            .Cells(7, 4).Value = x
            .Calculate
            Sheets("Resistance").Cells(r, 9).Resize(, 7).Value = .Cells(r, 2).Resize(, 7).Value
            r = r + 1
        Next x
    End With
    
    Sheets("Resistance").Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[rzgixxer]

Hi JackDanIce,

Thanks for your response!

It seems that Excel calculates one another, although I don't see any pasted values into the corresponding cells I50;O50 to I140:O140 of my "Sav. paper" sheet..
Currently the macro I need is works because I used the 'Record Macro' function, this works quite slow for a macro calculation.

Example:

Sheets("Sav. paper").Select

Range("D7").Select
ActiveCell.FormulaR1C1 = "10"
Range("B50").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("I50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("D7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "10.5"
Range("B51").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("I51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Etcetera...

I think your code will decrease calculation time of my tool, since the function is far smaller than mine currently.
Although I do not fully understand your code, so I don't know what is going wrong..

Is it possible to send you my file somehow? Perhaps you can take a look?

Thanks already!!

 

[JackDanIce]

You do not need to use copy and paste for making 1 cell's value equal another cell's value. In a new sheet, put any value in A1 and then run:

cells(1,2).value = cells(1,1).value

B1 should then contain value of A1

I've added a line to the code, try:

Sub Macro1()

    Dim x       As Double
    Dim r       As Long
    
    Application.ScreenUpdating = False
    r = 50
    
    With Sheets("Sav. paper")
        For x = 10 To 55 Step 0.5
            .Cells(7, 4).Value = x
            .Calculate
            DoEvents
            Sheets("Resistance").Cells(r, 9).Resize(, 7).Value = .Cells(r, 2).Resize(, 7).Value
            r = r + 1
        Next x
    End With
    
    Sheets("Resistance").Select
    
    Application.ScreenUpdating = True
    
End Sub

Per the board rules, you can't send files privately, but you can upload to a sharing site and post a link on this thread to it.

 

[rzgixxer]

Thanks for the information, this part of the code I do understand
Had to do this in order to 'record the macro'.

Still Excel is calculating, although there are no actual values visible into the corresponding cells..
Does the code paste answers for each speed, before it calculates the answers for the next speed?
When changing the speed value in my worksheet, all previous answers also change, therefore I copy-paste each calculated answer as a value in the next columns..

Do you recommend a site where I can drop the file, without us sharing any emails or log in information or so?
Maybe this makes it more understandable for you, what is happening in the document?

 

[Fluff]

If I'm understanding your requirements correctly, try this modified version of Jack' code

Sub Macro1()

    Dim x       As Double
    Dim r       As Long
    
    Application.ScreenUpdating = False
    r = 50
    
    With Sheets("Sav. paper")
        For x = 10 To 55 Step 0.5
            .Cells(7, 4).Value = x
            .Calculate
            DoEvents
            .Cells(r, 9).Resize(, 7).Value = .Cells(r, 2).Resize(, 7).Value
            r = r + 1
        Next x
    End With
    
    Sheets("Resistance").Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[rzgixxer]

That did the trick Fluff!!

Now I see, the previous code was directing the answers to the main sheet 'Resistance'.
By the addition of Fluff, the answers are directed and pasted into the cells of the correct sheet 'Sav.paper'.

Thanks to both of you, the calculation performed by this code is much faster than before!
I nice addition to my tool and thesis

Regards.

 

[JackDanIce]

Thanks @Fluff, my mistake, I thought results were to be printed into sheet Resistance, but reading again, can see OP meant Sav. Paper.

In my main worksheet "Resistance", I added a form control button where the macro is supposed to be assigned to.
What it should do...

1. It must select cell D7 in another worksheet called "Sav. paper" (this cell contains is a speed value, upcoming calculations are based on this value);
2. Cell D7 should start at a value of 10;
3. In cells B50:H50 the answers are given for the calculations with the speed of cell D7.
4. These answers need to be pasted as values into cells I50:O50

@rzgixxer the more precise and clear you can be, easier it is to provide a solution. Assume anyone reading your problem is blind and has no ability to see your PC monitor, so explain everything precisely and explicitly. Though ideally, succinctly too!