Solved

Changing a variable value

Posted on 2014-10-16
10
44 Views
Last Modified: 2014-10-20
I am using an inputbox to set a variable:

strLineItem = InputBox("Enter a valid line item for this sales order number") 'Inputbox text

I would like to set another variable based on this input and call it: strLineItemPlus100

This variable is going to be the strLineItem + 100  but called strLineItemPlus100

Two things.  One how would I make sure that strLineItem is an increment of one hundred.  Two how would I set the new value strLineItemPlus100?  so I can use that number later in the procedure?

Please advise and thanks.
0
Comment
Question by:RWayneH
  • 4
  • 3
  • 3
10 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40385372
Dim strLineItemPlus100 As Double
strLineItemPlus100 =  strLineItem + 100
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40385392
And if, say, strLineItem is 123.45 and you want it to be 100, then do this.

strLineItem = Int(strLineItem - strLineItem Mod 100)
0
 

Author Comment

by:RWayneH
ID: 40385394
And how do I make sure that strLineItem gets a value that is an increments of 100?
So if strLineItem is 500, that makes strLineItemPlus100 = to 600

What does the Dim of strLineItemPlus100 As Double mean.  The Double is throwing me off.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40385429
"Double" is a type of variable just like "String" except that Double is a numeric type.

Does this help?

Dim strLineItemPlus100 As Double
Dim strLineItem As String

' Set up the data for this example so that strLineItem has a value
strLineItem = 555.45

' This makes strLineItem = 500
strLineItem = Int(strLineItem - strLineItem Mod 100)

strLineItemPlus100 = strLineItem + 100

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40385431
Yep; dimensioning a variable with "str" as the prefix as a Double-precision variable is a little confusing.  
Maybe:
Dim dblLineItemPlus100 as Double

Open in new window

makes a little more sense.

However, it sounds like you want to assign a variable to the next highest multiple of 100 after the value inputted (strLineItem).  In this case:
Sub assign_next()
    Dim strLineItem As String
    Dim intLineItemPlus100 As Integer
    
    strLineItem = InputBox("Enter a valid line item for this sales order number") 'Inputbox text
    intLineItemPlus100 = WorksheetFunction.Round(Val(strLineItem), -2) + 100
     
End Sub

Open in new window


Regards,
-Glenn
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:RWayneH
ID: 40386848
How do I make sure that the strLineItem is a multiple of 100?   It has to be something that ends in 00
Like 500,  10600,  50000,.....   It need it to validate that before we start the procedure that it is a multiple of 100

so the above results for strLineItemPlus100 would be 600, 10700 and 50100.  Depending on what is loaded in the InputBox.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40386877
If you need BOTH of the variables to be multiples of 100 - with the second one being 100 greater than the first, then try this code:

    Dim strLineItem As String
    Dim intLineItemPlus100 As Integer
    
    strLineItem = InputBox("Enter a valid line item for this sales order number") 'Inputbox text
    strLineItem = WorksheetFunction.Round(Val(strLineItem),-2)
    intLineItemPlus100 = strLineItem + 100

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:RWayneH
ID: 40387244
Line 5 looks like it is rounding it...  is there a way not to use rounding at all.  It has to be exact.  A multiple of 100.  If it is not I need it to fail and try again on the inputbox.  Or will what you have work?  If 115 is entered, fail   If 165 entered fail..  It must be 100, 200, 300  etc.  It is critical for further use later in the procedure.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40387455
RWayneH,
The code I provided forces whatever value is entered to be rounded to the nearest 100.  Enter 115, you get 100.  Enter 165, you get 200.  And intLineItemPlus100 is automatically 100 more.

If you want to force the user to enter a value rounded to the 100's place then try this instead:
Option Explicit
Sub rounder()
    Dim intLineItem As String
    Dim intLineItemPlus100 As Integer
    Dim doit As Integer
    
InputLineItem:
    intLineItem = InputBox("Enter a valid line item for this sales order number") 'Inputbox text
    If intLineItem = "" Then Exit Sub
    On Error GoTo QuitIt
    If intLineItem <> WorksheetFunction.Round(Val(intLineItem), -2) Then
        doit = MsgBox("Please enter a value rounded to the nearest one hundred (100).", vbCritical + vbOKCancel, "Invalid Entry")
        If doit = vbCancel Then
            Exit Sub
        Else
            GoTo InputLineItem
        End If
    End If
    intLineItemPlus100 = intLineItem + 100
    
    'Test results:
    MsgBox "intLineItem: " & intLineItem & vbLf & _
           "intLineItemPlus100: " & intLineItemPlus100
QuitIt:
End Sub

Open in new window



-Glenn
0
 

Author Closing Comment

by:RWayneH
ID: 40393017
Works GREAT!!  Thanks for the help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now