+ Reply to Thread
Results 1 to 4 of 4

Check the value decimal

Hybrid View

ccs1981 Check the value decimal 09-22-2008, 07:18 AM
royUK Try removing the speech marks... 09-22-2008, 07:26 AM
ccs1981 thanks 09-23-2008, 12:11 AM
shg It could be tightened up a... 09-23-2008, 12:47 AM
  1. #1
    Registered User
    Join Date
    09-16-2008
    Location
    malaysia
    Posts
    89

    Check the value decimal

    hi all,
    i am facing a problem here.
    i hv a column "O" which its format cells is number with decimal places is 2

    when i click a command button, i want to have another value in column "P"

    Below is my coding:

    Private Sub CommandButton1_Click()
    
    For x = 7 To 16
    If Cells(x, "O").Value < "1.00" Then
    Cells(x, "P").Value = 0.5
    
    ElseIf Cells(x, "O").Value >= "1.00" And Cells(x, "O").Value < "1.50" Then
    Cells(x, "P").Value = 1
    
    ElseIf Cells(x, "O").Value >= "1.50" And Cells(x, "O").Value < "2.00" Then
    Cells(x, "P").Value = 1.5
    
    ElseIf Cells(x, "O").Value >= "2.00" And Cells(x, "O").Value < "2.50" Then
    Cells(x, "P").Value = 2
    
    ElseIf Cells(x, "O").Value >= "2.50" And Cells(x, "O").Value < "3.00" Then
    Cells(x, "P").Value = 2.5
    
    ElseIf Cells(x, "O").Value >= "3.00" And Cells(x, "O").Value < "3.50" Then
    Cells(x, "P").Value = 3
    
    ElseIf Cells(x, "O").Value >= "3.50" And Cells(x, "O").Value < "4.00" Then
    Cells(x, "P").Value = 3.5
    
    ElseIf Cells(x, "O").Value >= "4.00" And Cells(x, "O").Value < "4.50" Then
    Cells(x, "P").Value = 4
    
    ElseIf Cells(x, "O").Value >= "4.50" And Cells(x, "O").Value < "5.00" Then
    Cells(x, "P").Value = 4.5
    
    ElseIf Cells(x, "O").Value >= "5.00" And Cells(x, "O").Value < "5.50" Then
    Cells(x, "P").Value = 5
    
    ElseIf Cells(x, "O").Value >= "5.50" And Cells(x, "O").Value < "6.00" Then
    Cells(x, "P").Value = 5.5
    
    ElseIf Cells(x, "O").Value >= "6.00" And Cells(x, "O").Value < "7.00" Then
    Cells(x, "P").Value = 6
    
    ElseIf Cells(x, "O").Value >= "7.00" And Cells(x, "O").Value < "8.00" Then
    Cells(x, "P").Value = 7
    
    ElseIf Cells(x, "O").Value >= "8.00" And Cells(x, "O").Value < "9.00" Then
    Cells(x, "P").Value = 8
    
    ElseIf Cells(x, "O").Value >= "9.00" And Cells(x, "O").Value < "10.00" Then
    Cells(x, "P").Value = 9
    
    ElseIf Cells(x, "O").Value >= "10.00" Then
    Cells(x, "P").Value = 10
    
    Else
    Cells(x, "P").Value = ""
    
    End If
    
    Next
    End Sub

    but my problem is when my value in column "O" is "35" the the output come out in column "P" is 3.5, the answer should be 10,
    it seems like cannot check the value which is more than 10 and ignore the decimal in column "O".

    Can someone please help me. thanks.
    Last edited by VBA Noob; 09-22-2008 at 08:18 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try removing the speech marks around the numbers.
     Dim x As Integer
        For x = 7 To 16
            If Cells(x, 15).Value < 1 Then
                Cells(x, 16).Value = 0.5
    
            ElseIf Cells(x, 15).Value >= 1 And Cells(x, 15).Value < 1.5 Then
                Cells(x, 16).Value = 1
    
            ElseIf Cells(x, 15).Value >= 1.5 And Cells(x, 15).Value < 2# Then
                Cells(x, 16).Value = 1.5
    
            ElseIf Cells(x, 15).Value >= 2# And Cells(x, 15).Value < 2.5 Then
                Cells(x, 16).Value = 2
    
            ElseIf Cells(x, 15).Value >= 2.5 And Cells(x, 15).Value < 3# Then
                Cells(x, 16).Value = 2.5
    
            ElseIf Cells(x, 15).Value >= 3# And Cells(x, 15).Value < 3.5 Then
                Cells(x, 16).Value = 3
    
            ElseIf Cells(x, 15).Value >= 3.5 And Cells(x, 15).Value < 4# Then
                Cells(x, 16).Value = 3.5
    
            ElseIf Cells(x, 15).Value >= 4# And Cells(x, 15).Value < 4.5 Then
                Cells(x, 16).Value = 4
    
            ElseIf Cells(x, 15).Value >= 4.5 And Cells(x, 15).Value < 5# Then
                Cells(x, 16).Value = 4.5
    
            ElseIf Cells(x, 15).Value >= 5# And Cells(x, 15).Value < 5.5 Then
                Cells(x, 16).Value = 5
    
            ElseIf Cells(x, 15).Value >= 5.5 And Cells(x, 15).Value < 6# Then
                Cells(x, 16).Value = 5.5
    
            ElseIf Cells(x, 15).Value >= 6# And Cells(x, 15).Value < 7# Then
                Cells(x, 16).Value = 6
    
            ElseIf Cells(x, 15).Value >= 7# And Cells(x, 15).Value < 8# Then
                Cells(x, 16).Value = 7
    
            ElseIf Cells(x, 15).Value >= 8# And Cells(x, 15).Value < 9# Then
                Cells(x, 16).Value = 8
    
            ElseIf Cells(x, 15).Value >= 9# And Cells(x, 15).Value < 10# Then
                Cells(x, 16).Value = 9
    
            ElseIf Cells(x, 15).Value >= 10# Then
                Cells(x, 16).Value = 10
    
            Else
                Cells(x, 16).Value = ""
    
            End If
    
        Next
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    09-16-2008
    Location
    malaysia
    Posts
    89

    thanks

    thanks you so much.. it works

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    It could be tightened up a little:
    Sub x()
        Dim x       As Integer
        For x = 7 To 16
            With Cells(x, 16)
                Select Case Cells(x, 15).Value
                    Case Is >= 10:  .Value = 10
                    Case Is >= 9:   .Value = 9
                    Case Is >= 8:   .Value = 8
                    Case Is >= 7:   .Value = 7
                    Case Is >= 6:   .Value = 6
                    Case Is >= 5.5: .Value = 5.5
                    Case Is >= 5:   .Value = 5
                    Case Is >= 4.5: .Value = 4.5
                    Case Is >= 4:   .Value = 4
                    Case Is >= 3.5: .Value = 3.5
                    Case Is >= 3:   .Value = 3
                    Case Is >= 2.5: .Value = 2.5
                    Case Is >= 2:   .Value = 2
                    Case Is >= 1.5: .Value = 1.5
                    Case Is >= 1:   .Value = 1
                    Case Else:      .Value = 0.5
                End Select
            End With
        Next x
    End Sub
    ... or
        For x = 7 To 16
            Cells(x, 16).Value = WorksheetFunction.Lookup(Cells(x, 15), _
                Array(-1E+307, 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 6, 7, 8, 9, 10), _
                Array(0.5, 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 6, 7, 8, 9, 10))
        Next x
    Last edited by shg; 09-23-2008 at 01:19 AM.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2007 : help with check boxes
    By lmm07468 in forum Excel General
    Replies: 3
    Last Post: 03-04-2011, 01:40 PM
  2. Check Boxes & Linkedcell
    By MBigD011 in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 03-15-2010, 10:34 AM
  3. Replies: 2
    Last Post: 09-18-2008, 11:19 PM
  4. VBA; find string and copy
    By Bill Rudd in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-06-2008, 10:41 PM
  5. Bank rec v2, deleting matching check #'s
    By Jerhansen277 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2007, 11:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1