Modify command to support infinite values vs fixed amount

G

gabethegrape

New Member
Joined
Mar 3, 2009
Messages
38
I have a command (Primary1) that generates values based on a user-defined number of sessions (1-25). However, I need to change 25 to an infinite amount. Any ideas how to accomplish that based on the code shown below?
Thank you!
Gabe

Code: 
Private Sub Primary1()
Dim i As Double
Dim st As Double
Dim tg As Double
Dim Inc As Double
Dim Inr As Double
    'Dim P As Double
Dim k As Long
Dim lSessions As Long, lIntervals As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    lSessions = CLng(cboSessions.Value)
    lIntervals = CLng(cboIntervals.Value)
    If optManual.Value = True Then
        For k = 1 To 25
            madI(k) = ""
        Next k
    End If
    If optTarget.Value = True Then
        If txtTarget.Value <> "" And txtStart.Value <> "" Then
            If lIntervals = 1 Then
                If lSessions <= 2 Then
                    mlDivision = 1
                ElseIf lSessions <= 25 Then
                    mlDivision = lSessions - 1
                End If
            ElseIf lIntervals = 2 Then
                Select Case lSessions
                    Case 1 To 4
                        mlDivision = 1
                    Case 5 To 6
                        mlDivision = 2
                    Case 7 To 8
                        mlDivision = 3
                    Case 9 To 10
                        mlDivision = 4
                    Case 11 To 12
                        mlDivision = 5
                    Case 13 To 14
                        mlDivision = 6
                    Case 15 To 16
                        mlDivision = 7
                    Case 17 To 18
                        mlDivision = 8
                    Case 19 To 20
                        mlDivision = 9
                    Case 21 To 22
                        mlDivision = 10
                    Case 23 To 24
                        mlDivision = 11
                    Case 25
                        mlDivision = 12
                End Select
            ElseIf lIntervals = 3 Then
                Select Case lSessions
                    Case 1 To 6
                        mlDivision = 1
                    Case 7 To 9
                        mlDivision = 2
                    Case 10 To 12
                        mlDivision = 3
                    Case 13 To 15
                        mlDivision = 4
                    Case 16 To 18
                        mlDivision = 5
                    Case 19 To 21
                        mlDivision = 6
                    Case 22 To 24
                        mlDivision = 7
                    Case 25
                        mlDivision = 8
                End Select
            ElseIf lIntervals = 4 Then
                Select Case lSessions
                    Case 1 To 8
                        mlDivision = 1
                    Case 9 To 12
                        mlDivision = 2
                    Case 13 To 16
                        mlDivision = 3
                    Case 17 To 20
                        mlDivision = 4
                    Case 21 To 24
                        mlDivision = 5
                    Case 25
                        mlDivision = 6
                End Select
            ElseIf lIntervals = 5 Then
                Select Case lSessions
                    Case 1 To 10
                        mlDivision = 1
                    Case 11 To 15
                        mlDivision = 2
                    Case 16 To 20
                        mlDivision = 3
                    Case 21 To 25
                        mlDivision = 4
                End Select
            End If

            st = txtStart.Value
            tg = txtTarget.Value
            mdIncrease = (tg - st) / mlDivision
            Inc = mdIncrease

            If lIntervals = 1 Then
                madI(1) = st
                madI(2) = st + (Inc * 1)
                madI(3) = st + (Inc * 2)
                madI(4) = st + (Inc * 3)
                madI(5) = st + (Inc * 4)
                madI(6) = st + (Inc * 5)
                madI(7) = st + (Inc * 6)
                madI(8) = st + (Inc * 7)
                madI(9) = st + (Inc * 8)
                madI(10) = st + (Inc * 9)
                madI(11) = st + (Inc * 10)
                madI(12) = st + (Inc * 11)
                madI(13) = st + (Inc * 12)
                madI(14) = st + (Inc * 13)
                madI(15) = st + (Inc * 14)
                madI(16) = st + (Inc * 15)
                madI(17) = st + (Inc * 16)
                madI(18) = st + (Inc * 17)
                madI(19) = st + (Inc * 18)
                madI(20) = st + (Inc * 19)
                madI(21) = st + (Inc * 20)
                madI(22) = st + (Inc * 21)
                madI(23) = st + (Inc * 22)
                madI(24) = st + (Inc * 23)
                madI(25) = st + (Inc * 24)
            ElseIf lIntervals = 2 Then
                madI(1) = st
                madI(2) = st
                madI(3) = st + (Inc * 1)
                madI(4) = st + (Inc * 1)
                madI(5) = st + (Inc * 2)
                madI(6) = st + (Inc * 2)
                madI(7) = st + (Inc * 3)
                madI(8) = st + (Inc * 3)
                madI(9) = st + (Inc * 4)
                madI(10) = st + (Inc * 4)
                madI(11) = st + (Inc * 5)
                madI(12) = st + (Inc * 5)
                madI(13) = st + (Inc * 6)
                madI(14) = st + (Inc * 6)
                madI(15) = st + (Inc * 7)
                madI(16) = st + (Inc * 7)
                madI(17) = st + (Inc * 8)
                madI(18) = st + (Inc * 8)
                madI(19) = st + (Inc * 9)
                madI(20) = st + (Inc * 9)
                madI(21) = st + (Inc * 10)
                madI(22) = st + (Inc * 10)
                madI(23) = st + (Inc * 11)
                madI(24) = st + (Inc * 11)
                madI(25) = st + (Inc * 12)
            ElseIf lIntervals = 3 Then
                madI(1) = st
                madI(2) = st
                madI(3) = st
                madI(4) = st + (Inc * 1)
                madI(5) = st + (Inc * 1)
                madI(6) = st + (Inc * 1)
                madI(7) = st + (Inc * 2)
                madI(8) = st + (Inc * 2)
                madI(9) = st + (Inc * 2)
                madI(10) = st + (Inc * 3)
                madI(11) = st + (Inc * 3)
                madI(12) = st + (Inc * 3)
                madI(13) = st + (Inc * 4)
                madI(14) = st + (Inc * 4)
                madI(15) = st + (Inc * 4)
                madI(16) = st + (Inc * 5)
                madI(17) = st + (Inc * 5)
                madI(18) = st + (Inc * 5)
                madI(19) = st + (Inc * 6)
                madI(20) = st + (Inc * 6)
                madI(21) = st + (Inc * 6)
                madI(22) = st + (Inc * 7)
                madI(23) = st + (Inc * 7)
                madI(24) = st + (Inc * 7)
                madI(25) = st + (Inc * 8)
            ElseIf lIntervals = 4 Then
                madI(1) = st
                madI(2) = st
                madI(3) = st
                madI(4) = st
                madI(5) = st + (Inc * 1)
                madI(6) = st + (Inc * 1)
                madI(7) = st + (Inc * 1)
                madI(8) = st + (Inc * 1)
                madI(9) = st + (Inc * 2)
                madI(10) = st + (Inc * 2)
                madI(11) = st + (Inc * 2)
                madI(12) = st + (Inc * 2)
                madI(13) = st + (Inc * 3)
                madI(14) = st + (Inc * 3)
                madI(15) = st + (Inc * 3)
                madI(16) = st + (Inc * 3)
                madI(17) = st + (Inc * 4)
                madI(18) = st + (Inc * 4)
                madI(19) = st + (Inc * 4)
                madI(20) = st + (Inc * 4)
                madI(21) = st + (Inc * 5)
                madI(22) = st + (Inc * 5)
                madI(23) = st + (Inc * 5)
                madI(24) = st + (Inc * 5)
                madI(25) = st + (Inc * 6)
            ElseIf lIntervals = 5 Then
                madI(1) = st
                madI(2) = st
                madI(3) = st
                madI(4) = st
                madI(5) = st
                madI(6) = st + (Inc * 1)
                madI(7) = st + (Inc * 1)
                madI(8) = st + (Inc * 1)
                madI(9) = st + (Inc * 1)
                madI(10) = st + (Inc * 1)
                madI(11) = st + (Inc * 2)
                madI(12) = st + (Inc * 2)
                madI(13) = st + (Inc * 2)
                madI(14) = st + (Inc * 2)
                madI(15) = st + (Inc * 2)
                madI(16) = st + (Inc * 3)
                madI(17) = st + (Inc * 3)
                madI(18) = st + (Inc * 3)
                madI(19) = st + (Inc * 3)
                madI(20) = st + (Inc * 3)
                madI(21) = st + (Inc * 4)
                madI(22) = st + (Inc * 4)
                madI(23) = st + (Inc * 4)
                madI(24) = st + (Inc * 4)
                madI(25) = st + (Inc * 4)
            End If
        End If
    End If
    If optPercent.Value = True Then
        If optPercent.Value = True And txtPercent.Value = "" Then
            MsgBox ("Please Choose Percent Value")
            Exit Sub
        End If

        i = (txtPercent.Value / 100) + 1#
        st = txtStart.Value

        If lIntervals = 1 Then
            madI(1) = st
            madI(2) = st * (i ^ 1)
            madI(3) = st * (i ^ 2)
            madI(4) = st * (i ^ 3)
            madI(5) = st * (i ^ 4)
            madI(6) = st * (i ^ 5)
            madI(7) = st * (i ^ 6)
            madI(8) = st * (i ^ 7)
            madI(9) = st * (i ^ 8)
            madI(10) = st * (i ^ 9)
            madI(11) = st * (i ^ 10)
            madI(12) = st * (i ^ 11)
            madI(13) = st * (i ^ 12)
            madI(14) = st * (i ^ 13)
            madI(15) = st * (i ^ 14)
            madI(16) = st * (i ^ 15)
            madI(17) = st * (i ^ 16)
            madI(18) = st * (i ^ 17)
            madI(19) = st * (i ^ 18)
            madI(20) = st * (i ^ 19)
            madI(21) = st * (i ^ 20)
            madI(22) = st * (i ^ 21)
            madI(23) = st * (i ^ 22)
            madI(24) = st * (i ^ 23)
            madI(25) = st * (i ^ 24)
        ElseIf lIntervals = 2 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st * (i ^ 1)
            madI(4) = st * (i ^ 1)
            madI(5) = st * (i ^ 2)
            madI(6) = st * (i ^ 2)
            madI(7) = st * (i ^ 3)
            madI(8) = st * (i ^ 3)
            madI(9) = st * (i ^ 4)
            madI(10) = st * (i ^ 4)
            madI(11) = st * (i ^ 5)
            madI(12) = st * (i ^ 5)
            madI(13) = st * (i ^ 6)
            madI(14) = st * (i ^ 6)
            madI(15) = st * (i ^ 7)
            madI(16) = st * (i ^ 7)
            madI(17) = st * (i ^ 8)
            madI(18) = st * (i ^ 8)
            madI(19) = st * (i ^ 9)
            madI(20) = st * (i ^ 9)
            madI(21) = st * (i ^ 10)
            madI(22) = st * (i ^ 10)
            madI(23) = st * (i ^ 11)
            madI(24) = st * (i ^ 11)
            madI(25) = st * (i ^ 12)
        ElseIf lIntervals = 3 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st * (i ^ 1)
            madI(5) = st * (i ^ 1)
            madI(6) = st * (i ^ 1)
            madI(7) = st * (i ^ 2)
            madI(8) = st * (i ^ 2)
            madI(9) = st * (i ^ 2)
            madI(10) = st * (i ^ 3)
            madI(11) = st * (i ^ 3)
            madI(12) = st * (i ^ 3)
            madI(13) = st * (i ^ 4)
            madI(14) = st * (i ^ 4)
            madI(15) = st * (i ^ 4)
            madI(16) = st * (i ^ 5)
            madI(17) = st * (i ^ 5)
            madI(18) = st * (i ^ 5)
            madI(19) = st * (i ^ 6)
            madI(20) = st * (i ^ 6)
            madI(21) = st * (i ^ 6)
            madI(22) = st * (i ^ 7)
            madI(23) = st * (i ^ 7)
            madI(24) = st * (i ^ 7)
            madI(25) = st * (i ^ 8)
        ElseIf lIntervals = 4 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st
            madI(5) = st * (i ^ 1)
            madI(6) = st * (i ^ 1)
            madI(7) = st * (i ^ 1)
            madI(8) = st * (i ^ 1)
            madI(9) = st * (i ^ 2)
            madI(10) = st * (i ^ 2)
            madI(11) = st * (i ^ 2)
            madI(12) = st * (i ^ 2)
            madI(13) = st * (i ^ 3)
            madI(14) = st * (i ^ 3)
            madI(15) = st * (i ^ 3)
            madI(16) = st * (i ^ 3)
            madI(17) = st * (i ^ 4)
            madI(18) = st * (i ^ 4)
            madI(19) = st * (i ^ 4)
            madI(20) = st * (i ^ 4)
            madI(21) = st * (i ^ 5)
            madI(22) = st * (i ^ 5)
            madI(23) = st * (i ^ 5)
            madI(24) = st * (i ^ 5)
            madI(25) = st * (i ^ 6)
        ElseIf lIntervals = 5 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st
            madI(5) = st
            madI(6) = st * (i ^ 1)
            madI(7) = st * (i ^ 1)
            madI(8) = st * (i ^ 1)
            madI(9) = st * (i ^ 1)
            madI(10) = st * (i ^ 1)
            madI(11) = st * (i ^ 2)
            madI(12) = st * (i ^ 2)
            madI(13) = st * (i ^ 2)
            madI(14) = st * (i ^ 2)
            madI(15) = st * (i ^ 2)
            madI(16) = st * (i ^ 3)
            madI(17) = st * (i ^ 3)
            madI(18) = st * (i ^ 3)
            madI(19) = st * (i ^ 3)
            madI(20) = st * (i ^ 3)
            madI(21) = st * (i ^ 4)
            madI(22) = st * (i ^ 4)
            madI(23) = st * (i ^ 4)
            madI(24) = st * (i ^ 4)
            madI(25) = st * (i ^ 4)
        End If
    End If
    If optIncremental.Value = True Then
        If optIncremental.Value = True And txtIncrements.Value = "" Then
            MsgBox ("Please Choose Increment Value")
            Exit Sub
        End If

        Inr = txtIncrements.Value
        st = txtStart.Value

        If lIntervals = 1 Then
            madI(1) = st
            madI(2) = st + Inr
            madI(3) = st + (Inr * 2)
            madI(4) = st + (Inr * 3)
            madI(5) = st + (Inr * 4)
            madI(6) = st + (Inr * 5)
            madI(7) = st + (Inr * 6)
            madI(8) = st + (Inr * 7)
            madI(9) = st + (Inr * 8)
            madI(10) = st + (Inr * 9)
            madI(11) = st + (Inr * 10)
            madI(12) = st + (Inr * 11)
            madI(13) = st + (Inr * 12)
            madI(14) = st + (Inr * 13)
            madI(15) = st + (Inr * 14)
            madI(16) = st + (Inr * 15)
            madI(17) = st + (Inr * 16)
            madI(18) = st + (Inr * 17)
            madI(19) = st + (Inr * 18)
            madI(20) = st + (Inr * 19)
            madI(21) = st + (Inr * 20)
            madI(22) = st + (Inr * 21)
            madI(23) = st + (Inr * 22)
            madI(24) = st + (Inr * 23)
            madI(25) = st + (Inr * 24)
        ElseIf lIntervals = 2 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st + Inr
            madI(4) = st + Inr
            madI(5) = st + (Inr * 2)
            madI(6) = st + (Inr * 2)
            madI(7) = st + (Inr * 3)
            madI(8) = st + (Inr * 3)
            madI(9) = st + (Inr * 4)
            madI(10) = st + (Inr * 4)
            madI(11) = st + (Inr * 5)
            madI(12) = st + (Inr * 5)
            madI(13) = st + (Inr * 6)
            madI(14) = st + (Inr * 6)
            madI(15) = st + (Inr * 7)
            madI(16) = st + (Inr * 7)
            madI(17) = st + (Inr * 8)
            madI(18) = st + (Inr * 8)
            madI(19) = st + (Inr * 9)
            madI(20) = st + (Inr * 9)
            madI(21) = st + (Inr * 10)
            madI(22) = st + (Inr * 10)
            madI(23) = st + (Inr * 11)
            madI(24) = st + (Inr * 11)
            madI(25) = st + (Inr * 12)
        ElseIf lIntervals = 3 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st + Inr
            madI(5) = st + Inr
            madI(6) = st + Inr
            madI(7) = st + (Inr * 2)
            madI(8) = st + (Inr * 2)
            madI(9) = st + (Inr * 2)
            madI(10) = st + (Inr * 3)
            madI(11) = st + (Inr * 3)
            madI(12) = st + (Inr * 3)
            madI(13) = st + (Inr * 4)
            madI(14) = st + (Inr * 4)
            madI(15) = st + (Inr * 4)
            madI(16) = st + (Inr * 5)
            madI(17) = st + (Inr * 5)
            madI(18) = st + (Inr * 5)
            madI(19) = st + (Inr * 6)
            madI(20) = st + (Inr * 6)
            madI(21) = st + (Inr * 6)
            madI(22) = st + (Inr * 7)
            madI(23) = st + (Inr * 7)
            madI(24) = st + (Inr * 7)
            madI(25) = st + (Inr * 8)
        ElseIf lIntervals = 4 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st
            madI(5) = st + Inr
            madI(6) = st + Inr
            madI(7) = st + Inr
            madI(8) = st + Inr
            madI(9) = st + (Inr * 2)
            madI(10) = st + (Inr * 2)
            madI(11) = st + (Inr * 2)
            madI(12) = st + (Inr * 2)
            madI(13) = st + (Inr * 3)
            madI(14) = st + (Inr * 3)
            madI(15) = st + (Inr * 3)
            madI(16) = st + (Inr * 3)
            madI(17) = st + (Inr * 4)
            madI(18) = st + (Inr * 4)
            madI(19) = st + (Inr * 4)
            madI(20) = st + (Inr * 4)
            madI(21) = st + (Inr * 5)
            madI(22) = st + (Inr * 5)
            madI(23) = st + (Inr * 5)
            madI(24) = st + (Inr * 5)
            madI(25) = st + (Inr * 6)
        ElseIf lIntervals = 5 Then
            madI(1) = st
            madI(2) = st
            madI(3) = st
            madI(4) = st
            madI(5) = st
            madI(6) = st + Inr
            madI(7) = st + Inr
            madI(8) = st + Inr
            madI(9) = st + Inr
            madI(10) = st + Inr
            madI(11) = st + (Inr * 2)
            madI(12) = st + (Inr * 2)
            madI(13) = st + (Inr * 2)
            madI(14) = st + (Inr * 2)
            madI(15) = st + (Inr * 2)
            madI(16) = st + (Inr * 3)
            madI(17) = st + (Inr * 3)
            madI(18) = st + (Inr * 3)
            madI(19) = st + (Inr * 3)
            madI(20) = st + (Inr * 3)
            madI(21) = st + (Inr * 4)
            madI(22) = st + (Inr * 4)
            madI(23) = st + (Inr * 4)
            madI(24) = st + (Inr * 4)
            madI(25) = st + (Inr * 4)
        End If
    End If
    If txtPrimPlatEnd.Value <> "" Then
        If txtPrimPlatEnd.Value > 25 Then
            MsgBox ("Choose a plateau End value up to 25")
            Exit Sub
        End If
    End If
    Application.ScreenUpdating = True
    Secondary
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Ok, I believe I've found a way to condense most of it with loops, but now I'm struggling with the Case/Select section:

Code: 
                  Select Case lSessions
                        Case 1 To 4
                            mlDivision = 1
                        Case 5 To 6
                            mlDivision = 2
                        Case 7 To 8
                            mlDivision = 3
                        Case 9 To 10
                            mlDivision = 4
                        Case 11 To 12
                            mlDivision = 5
                        Case 13 To 14
                            mlDivision = 6
                        Case 15 To 16
                            mlDivision = 7
                        Case 17 To 18
                            mlDivision = 8
                        Case 19 To 20
                            mlDivision = 9
                        Case 21 To 22
                            mlDivision = 10
                        Case 23 To 24
                            mlDivision = 11
                        Case 25
                            mlDivision = 12
                    End Select
                ElseIf lIntervals = 3 Then
                    Select Case lSessions
                        Case 1 To 6
                            mlDivision = 1
                        Case 7 To 9
                            mlDivision = 2
                        Case 10 To 12
                            mlDivision = 3
                        Case 13 To 15
                            mlDivision = 4
                        Case 16 To 18
                            mlDivision = 5
                        Case 19 To 21
                            mlDivision = 6
                        Case 22 To 24
                            mlDivision = 7
                        Case 25
                            mlDivision = 8
                    End Select
                ElseIf lIntervals = 4 Then
                    Select Case lSessions
                        Case 1 To 8
                            mlDivision = 1
                        Case 9 To 12
                            mlDivision = 2
                        Case 13 To 16
                            mlDivision = 3
                        Case 17 To 20
                            mlDivision = 4
                        Case 21 To 24
                            mlDivision = 5
                        Case 25
                            mlDivision = 6
                    End Select
                ElseIf lIntervals = 5 Then
                    Select Case lSessions
                        Case 1 To 10
                            mlDivision = 1
                        Case 11 To 15
                            mlDivision = 2
                        Case 16 To 20
                            mlDivision = 3
                        Case 21 To 25
                            mlDivision = 4
                    End Select
                End If
 
Upvote 0
I was able to use nested loop statements and the code below for the case/select to solve this problem. Thank you for anyone who tried :) I appreciate it.

Code: 
                    i = 2
                    For c = 5 To lSessions Step 2
                    mlDivision = i
                    mlDivision = i
                    i = i + 1
                    Next c
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Solved
For 2 Columns how to Wraprows and then sort it horizontally accordingly with the rank List?
Replies
7
Views
277
Lacan
Lacan
K
  • Question Question
Net work Hours Calculation
Replies
5
Views
383
Keyan_rrr
K
Lacan
  • Solved
Let formula?
2
Replies
12
Views
458
Lacan
Lacan
T
  • Question Question
VBA Macro suddenly extremely slow. Need help in how to edit it.
Replies
3
Views
263
bobsan42
bobsan42
M
  • Question Question
Sum a cell on multiple sheets based on two or more criteria
Replies
1
Views
39
mad3
M

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

Share this page

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