My cool procedure not giving me cute results-I need help with logical arguments

[kelly mort]

This part of the code is not giving me cute result.

If SysNum(i) < SysNum(i + 1) Then
      MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must be in descending order!", vbExclamation
      SysFlg = True
Else
      SysFlg = False
 End If

My aim was to get a user input of integer values from one to five (maximum) numbers, which means it could be from 1 – 5 numbers. And these numbers must be integers, no floating numbers or decimals. Then the first number must always be 100. Then these numbers, if more than 1, must be in descending order. So I came up with this cool procedure, lol, meanwhile, my goals were not met. As I pointed out from the above, I am not getting the cutest outcome I needed. I need tech support with it.


Now, when I am able to get it working, my next goal was to store those numbers from the user input to a variable by multiplying each number in that array by 10.

That’s if I successfully entered “100,90,85,70”, which I convert to “100 90 85 70”, then my variable, MyVar should store “1000 900 850 700”. I am very grateful for your help. Thanks

Sub MyProcedureNotWorking()
Dim SysNum , SysFlg As Boolean
SysFlg = True
                While SysFlg = True
                SysNum = InputBox("Enter the numbers,eg. 100,95,90 or 100 95 90 [Not more than 5 numbers”)
                
                SysNum = Application.Trim(SysNum)
                If SysNum = "" Then Exit Sub
                
                SysNum = Replace(SysNum, ",", " ")
                SysNum = Application.Trim(SysNum)
                SysNum = Split(SysNum)
                
                If UBound(SysNum) > 0 Then
                    For i = 0 To UBound(SysNum)
                        If Not IsNumeric(SysNum(i)) Then
                            MsgBox SysNum(i) & " is not a valid number, change it", vbExclamation
                        Else
                            Select Case i
                                Case 0 To UBound(SysNum) ' - 1
                                    If SysNum(0) <> 100 Then
                                        MsgBox "The first number must be 100!", vbExclamation
                                    Else
                                        SysFlg = False
                                    End If
                                    
                                    If SysNum(i) < SysNum(i + 1) Then
                                        MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must be in descending order!", vbExclamation
                                        SysFlg = True
                                    Else
                                        SysFlg = False
                                    End If
                            End Select
                        End If
                    Next i
                ElseIf UBound(SysNum) = 0 Then
                    If Not IsNumeric(SysNum(0)) Then
                        MsgBox SysNum(0) & " is not a valid number, change it", vbExclamation
                    Else
                        If SysNum(0) <> 100 Then
                            MsgBox "The first number must be 100!", vbExclamation
                        Else
                            SysFlg = False
                        End If
                    End If
                End If
                Wend
End Sub

 

[jasonb75]

I haven't been through all of the code, but a couple of slight changes to your Select Case block, with an extra line after it for the multiplier.

Select Case i
    Case 0
        If SysNum(0) <> 100 Then
            MsgBox "The first number must be 100!", vbExclamation
        Else
            SysFlg = False
        End If
    Case 1 To UBound(SysNum) - 1
        If SysNum(i) < SysNum(i + 1) Then
            MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must be in descending order!", vbExclamation
            SysFlg = True
        ElseIf SysNum(i) <> CLng(SysNum(i)) Then
            MsgBox SysNum(i) & "Is not an integer!"
            SysFlf = True
        Else
            SysFlg = False
        End If
End Select
If SysFlg = False Then SysNum(i) = SysNum(i) * 10

 

[kelly mort]

Cute!!! Working very smart and great. But this line down here is not producing result as I want. Where and how do I store the converted values to the MyVar variable?

And in case I enter same numbers multiple times, how will I verify that cutely? Like 100, 100, 90.....?

If SysFlg = False Then SysNum(i) = SysNum(i) * 10

 

[jasonb75]

And in case I enter same numbers multiple times, how will I verify that cutely? Like 100, 100, 90.....?

Try changing < to <= in the line

If SysNum(i) < SysNum(i + 1) Then

Where and how do I store the converted values to the MyVar variable?

So SysNum should retain the original numbers and MyVar should hold the multiplied numbers? Try something like.

If SysFlg = False Then MyVar(i) = SysNum(i) * 10

 

[kelly mort]

Okay it worked fine. Except when I have 100, 90, 100; it does not work. It just drops the loop

Case 1 To UBound(SysNum) - 1

When I have only two numbers , where the ubound is 1, the code fails. It drops even when there is an invalid entry.
I have stared at it for a while, can't seem to figure it out, how to fix it.

If SysFlg = False Then MyVar(i) = SysNum(i) * 10

This line is not working for me. I placed it after the end select block.

This is how the final structure of the stored values should be like:
MyVar= “1000 900 850 700”

 

[Akuini]

Try this:

Sub MyProcedureNotWorking1()
Dim SysNum
Dim i As Long, myVar As String, txt As String

    Do
        SysNum = InputBox("Enter the numbers,eg. 100,95,90 or 100 95 90 [Not more than 5 numbers", , txt)
        
        txt = SysNum
        If SysNum = "" Then Exit Sub
        
        If InStr(SysNum, ".") Then
            MsgBox "The number must be integer!", vbExclamation
            GoTo skip:
        End If

        SysNum = Replace(SysNum, ",", " ")
        SysNum = WorksheetFunction.Trim(SysNum)
        
        If Not (SysNum = "100" Or Left(SysNum, 4) = "100 ") Then
            MsgBox "The first number must be 100!", vbExclamation
            GoTo skip:
        End If
        
        If SysNum = "100" Then
            myVar = 100 * 10: Exit Do
        
        Else
            
            SysNum = Split(SysNum)
            
            If UBound(SysNum) > 4 Then
                MsgBox "Max 5 numbers only", vbExclamation
                GoTo skip:
            End If
            
            For i = LBound(SysNum) To UBound(SysNum) - 1
                If IsNumeric((SysNum(i))) And IsNumeric((SysNum(i + 1))) Then
                    If Val(SysNum(i)) < Val(SysNum(i + 1)) Then
                    MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must be in descending order!", vbExclamation
                     GoTo skip:
                    End If
                Else
                    MsgBox SysNum(i + 1) & " is not a valid number, change it", vbExclamation
                      GoTo skip:
                End If
            Next
            
            For i = LBound(SysNum) To UBound(SysNum)
                SysNum(i) = SysNum(i) * 10
            Next
            
            myVar = Join(SysNum, " ")
            Exit Do
        
        End If
        
skip:
    Loop
                
        MsgBox myVar
End Sub

 

[kelly mort]

Try this:

            For i = LBound(SysNum) To UBound(SysNum) - 1
                If IsNumeric((SysNum(i))) And IsNumeric((SysNum(i + 1))) Then
                    If Val(SysNum(i)) < Val(SysNum(i + 1)) Then
                    MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must be in descending order!", vbExclamation
                     GoTo skip:
                    

                    ElseIf Val(SysNum(i)) = Val(SysNum(i + 1)) Then
                    MsgBox SysNum(i) & " " & SysNum(i + 1) & " The numbers must not be the same", vbExclamation
                     GoTo skip:
                    End If
                Else
                    MsgBox SysNum(i + 1) & " is not a valid number, change it", vbExclamation
                      GoTo skip:
                End If
            Next

The code worked brilliantly, I fixed it as above to handle same numbers appearing. Let me know if that is the way to go with it.

Very grateful. Thanks


Edit:

How do I get the original inputbox value display in a message alert as you did with the MyVar variable?

 

[Akuini]

I fixed it as above to handle same numbers appearing. Let me know if that is the way to go with it.

That's ok.

How do I get the original inputbox value display in a message alert as you did with the MyVar variable?

It's in the variable txt .

MsgBox txt MsgBox myVar

 

[kelly mort]

That's ok.


It's in the variable txt .

MsgBox txt MsgBox myVar

You are a legend!

It's working very smart now.

Have a nice time

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.