Macro to Track Multiple Variables

0

I have Been Using the Following Code to Control a Macro.

Sub Run_Test() 

' Run_Test 

i = Range("_B3_Percent").Value

Do While i < 0.75

Back_3_Success

i = Range("_B3_Percent").Value

Loop

End Sub

This Control Runs a Macro called "Back_3_Success" until The Range "_B3_Percent" is Greater that 75% and Stops. 

What I need is to be able to Add a Macro "Paste_Results" into the process, so that the Results will be pasted and then the Run_Test Macro will Start again and Search for another result that is greater than 75%, and again Paste the Result to the List. 

I also need another Code that will track 2 Ranges and Stop when they are equal.  The Second Range is Called "RunOver"

Any Help would be Greatly Appreciated.

Answer
Discuss

Discussion

Thank You for the Response.  I am unortunately almost ignorant when it comes to VBA Code.  It will take ma a while to understand what you provided as to the Multiple Variables Method
The First Code for Run_Test does not work.  There is no mention of the Back_3_Success macro or the Second Macro that is Called "Paste_Results"
The Goal is to Run Back_3_Results until the Value of _B3_Percent is Greater than .75, Then Run Paste Results 1 Time and Then Restart the Test.

What I need is to be able to Add a Macro called "Paste_Results" into the process, so that the Results will be pasted and then the Run_Test Macro will Start again and Search for another result that is greater than 75%, and again Paste the Result to the List. 
AllenB Oct 12, '17 at 11:11 pm
Let's set a few rules for this correspondence. (1) Please refer to the three sets of code as one, two and three. That would make "First Code for Run_Test" the 'Run_Test' procedure in the first code set. (2) "Doesn't work" is unhelpful. If it crashes a line of code will be highlighted by the Debugger. Please always mention that line of code, plus any other observations you may wish to impart.

I didn't provide a macro called "Paste_Results". Perhaps the name of that macro is less important than the function it is supposed to perform. It's a good idea to study the code first, absorb the new ideas I have included, and then look for additions perhaps in another thread. In this thread we should limit ourselves to getting the loop to work for you.

I have constructed this loop without much information from you. I presumed that you have many values in column B and that might be quite wrong. If your worksheet is structured differently, the point to start is to explain the structure of the worksheet. You might attach a copy to your original question.
Variatus (rep: 1108) Oct 12, '17 at 11:39 pm
The Macro is "Called Run Test"
Code 1 is Called "Back_3_Success"
Code 2 is Called "Paste_Results"
When Code 1 is Running it sets the Range Value "_B_3_Percent"
When _B_3_Percent is greater than 75%, I would like Code 2 to Start and Paste The Result.  In addition to pasting the values  Code 2 advances the counter and determines a new value of _B_3_Percent.  I would Like to then Loop Back and Start Code 1 again.

I have already written Codes 1 and 2.  I tried to Create a macro by recording steps but when I include Code 1 by Reference it does not run.
AllenB Oct 13, '17 at 1:17 am
Add to Discussion

Answers

0

Before I answer your question let me suggest a few changes to your current setup. For one, you should declare all variables. Adding "Option Explicit" at the top of the sheet helps do this, avoids typos, and generally prolongs your useful life. Then, your code should have a function, like this.

Option Explicit
Sub Run_Test()
    
    Dim i As Double
    
    i = Range("_B3_Percent").Value
    Do While i < 0.75
        i = Back_3_Success(i)
    Loop
End Sub

Private Function Back_3_Success(ByVal i As Double) As Double

    ' at start i = Range("_B3_Percent").Value
    ' the following simulates your calculations to reduce i
    i = i - 0.1
    Range("_B3_Percent").Value = i
    Back_3_Success = i
End Function

The function is important in the loop because it shows how the value i gets changed, not discernible in your original code. The other thing it does is to avoid accessing the worksheet multiple times. Each read/write takes a lot of time. Doing the same thing with a variable is at least 50 times faster.

Now, in order to look at multiple rows, one after the other, you need to get rid of the fixed range name and substitute it with a variable range. Actually, you don't need a range at all. All that's required is a row number.

With this technique added to the original code the result will look like this.

Option Explicit
Sub Run_Test()
    
    Dim i As Double
    Dim Rl As Long                      ' last row
    Dim R As Long
    
    With ActiveSheet
        ' find the last filled cell in column B
        ' and assign its row number to the variable Rl
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        ' now loop through all rows from 3rd row to Rl
        For R = 3 To Rl
            i = .Cells(R, "B").Value
            Do While i < 0.75
                i = Back_3_Success(i, R)
            Loop
        Next R
    End With
End Sub
Private Function Back_3_Success(ByVal i As Double, _
                                R As Long) As Double
    ' at start i = Cells(R, "B").Value
    ' the following simulates your calculations to reduce i
    i = i - 0.1
    ActiveSheet.Cells(R, "B").Value = i
    Back_3_Success = i
End Function

You can use idential code but looking for values >0.75. Or you could make a small modification in the above to the same effect.

Option Explicit
Sub Run_Test()
    
    Dim i As Double
    Dim Rl As Long                      ' last row
    Dim R As Long
    
    With ActiveSheet
        ' find the last filled cell in column B
        ' and assign its row number to the variable Rl
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        ' now loop through all rows from 3rd row to Rl
        For R = 3 To Rl
            i = .Cells(R, "B").Value
            If i > 0.75 Then
                Do
                    i = Forward_3_Success(i, R)
                Loop While i > 0.75
            Else
                Do While i < 0.75
                    i = Back_3_Success(i, R)
                Loop
            End If
        Next R
    End With
End Sub
Private Function Back_3_Success(ByVal i As Double, _
                                R As Long) As Double
    ' at start i = Cells(R, "B").Value
    ' the following simulates your calculations to reduce i
    i = i - 0.1
    ActiveSheet.Cells(R, "B").Value = i
    Back_3_Success = i
End Function
Private Function Forward_3_Success(ByVal i As Double, _
                                   R As Long) As Double
    ' at start i = Cells(R, "B").Value
    ' the following simulates your calculations to increase i
    i = i + 0.1
    ActiveSheet.Cells(R, "B").Value = i
    Forward_3_Success = i
End Function

Now the code calls either the 'Forward' or 'Back' version of the function where the value in column B is modified until it equals 0.75.

Discuss

Discussion

Focus on the functionality of code instead of the procedures' names. Your code 1 ("Back_3_Success") needs to be a function, not a sub. In my first code set I have changed that. You need to understand and adapt that code.

You haven't published your own code of "Back_3_Success". Therefore my version of that simulates its actions. You should replace the line "i = i - 0.1" with the code you have in your version of this procedure doing the same thing (or something with similar effect). If you need help with that please post your code.

You will not be able to advance to the next step until you have mastered this first set of code. You should make it do exactly the same thing your existing code can do, however in its own, different way. You will need the functionality of that difference for the next step.
Variatus (rep: 1108) Oct 13, '17 at 5:35 am
I appreciate you Response.  Here is what I am trying to do. Code 1  This Code is Really a Counter.  The Range "_B3_Next" (Type!$A$6) contains the Formula =_B3_Code+1.  Range "_B3_Next" is then copied and it's value is pasted in Range "_B3_Code" (Type!$A$7).  This advances "_B3_Code" by 1 and Creates a New Value in "B3_Percent" (Type!$H$1).   Code 2  This Code Copies the Range "Back_Block" (Type!$AL$2:$AN$2).  Using a Relative Reference, it is pasted by an End.Down followed by a Down to Paste the Values only at the Bottom of the List.  It then goes to the Range "B3_Next" (Type!$A$6), Copies and Pastes the Value in "B3_Code" (Type!$A$7).  The Previous Sentence describes the same thing that Code 1  Does and Changes the "B3_Percent" (Type!$H$1) Value.  If the "B3_Percent" Value is less than or equal to 0.75, Code 1 would restart,  If "B3_Percent is greater than 0.75, Code 2 would Run again.  In addition I would Like to be able to stop the Macro when the Range "_B3_Code" is greater than 999999. The Starting Value of "_B3_Code" is 10203. Thanks for Your Response.  Your help will be greatly appreciated.
AllenB Oct 13, '17 at 12:52 pm
This advances "_B3_Code" by 1 and Creates a New Value in _B3_Percent" (Type!$H$1).
Two questions. (1) How does the action you describe create a new value in H1? (2) Are both B3_Code and B3_Next required displays in your worksheet?

If the "B3_Percent" Value is less than or equal to 0.75, Code 1 would restart,  If "B3_Percent is greater than 0.75, Code 2 would Run again.
This implies three things. (1) That Code 1 would increase H1. Compare my above question. (2) That Code 2 would decrease H1. (3) That the code never stops running because H1 will always be <=> 75%.
BTW, if you google for End(xlDown) you will find lots of recommendations never to use it. Always use xlUp.
Variatus (rep: 1108) Oct 13, '17 at 11:12 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login