Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Bloomington, Indiana, USA
    Thanked 0 Times in 0 Posts

    VBA Code to change values over 40 (2003)

    I am fairly new to VBA, being self-taught mainly through the pieces of code I find on this forum, but I am having trouble with a piece of code. I have a spreadsheet that captures employee hours from an external source. Part of the problem with the source data is that, although it recognizes hours over 40 as overtime and populates the overtime column with the appropriate number of hours, it also leaves the overtime hours on top of the 40 in the regular hours column. I have written this macro to look for the hours over 40 and change the value to 40. There are 2 columns that have to be reviewed. Week 1 (H)and Week 2 (I). Additionally, and suggestions to clean up this code would be appreciated.

    The macro seemed to work fine until I noticed that it was also changing other non-overtime hours to 40. I have attached a copy of the workbook that has had names removed.

    Sub Find_OVT()
    'this code will find reg hours over 40 and limit to 40 hours.
    Dim i As Long
    Dim n As Long
    Dim wshSource As Worksheet

    Const strWk1Col = "H"
    Const strWk2Col = "I"

    Set wshSource = Worksheets(InputBox("select a worksheet name: "))
    n = wshSource.Range(strWk1Col & 65536).End(xlUp).Row
    For i = n To 2 Step -1
    If wshSource.Range(strWk1Col & i) > "40.00" Then
    wshSource.Range(strWk1Col & i) = "40.00"
    End If
    Next i
    n = wshSource.Range(strWk2Col & 65536).End(xlUp).Row
    For i = n To 2 Step -1
    If wshSource.Range(strWk2Col & i) > "40" Then
    wshSource.Range(strWk2Col & i) = "40"
    End If
    Next i

    End Sub

    Thanks in advance for any assistance that can be provided.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: VBA Code to change values over 40 (2003)

    Since the values in column H and I are numbers, you shouldn't use quotes around the value 40 (or 40.00) in the code. By using quotes, you are forcing Excel to perform an alphanumeric comparison instead of a numeric comparison. When you do that, "5.8" is larger than "40"! So use

    If wshSource.Range(strWk1Col & i) > 40 Then
    wshSource.Range(strWk1Col & i) = 40
    End If

    and similar for the other If ... End If.

Posting Permissions

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