+ Reply to Thread
Results 1 to 6 of 6

Need loop structure to get round limited IF statements

  1. #1
    Registered User
    Join Date
    11-09-2004
    Posts
    11

    Need loop structure to get round limited IF statements

    I'm completely stuck. I have a spreadsheet that has a worksheet for each month of the year plus an extra one called TOTALS.

    In each month there is one row per employee (there are about 20-30 employees) and the columns represent the days of the month. Its a spreadsheet to calculate holiday totals.

    The idea is: the holdays get entered with a '1' and in the TOTALS list there's a drop down of the names of the employees. The select their name and the total appear for each month. I've tried nested IF's but I'M limited to 7. I don'T have much programming knowlege and I can'T figure out how to solve this problem.

    I attach a zip file containing my example

    I'm grateful for any help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-24-2005
    Posts
    63
    If you want to code in VBA, you can use select case rather than If. Here is a sample code. But like to want you that I'm beginner myself. Just like to try some coding.

    To see the result, put one command button in your "totals" sheet. Then execute below procedure with that button. This only work for the first two person and until Mac only. You can add if you think okey. But I think there a lot of ways to do much better.

    Sub GetMonthlyData()

    Dim strName As Variant 'String 'employee Name
    Dim strmonth As String 'Month
    Dim lngCol As Long 'column number
    Dim lngRow As Long 'row number

    strName = Range("I2").Value
    ClearPreviousData
    Select Case strName
    Case "Blobby"

    'January
    lngRow = 6
    For lngCol = 2 To 32 'your date column
    With Sheets("Jan 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    'February
    lngRow = 10
    For lngCol = 2 To 32 'your date column
    With Sheets("Feb 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    'March
    lngRow = 14
    For lngCol = 2 To 32 'your date column
    With Sheets("Mar 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    Case "Doodle"

    'January
    lngRow = 6
    For lngCol = 2 To 32 'your date column
    With Sheets("Jan 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    'February
    lngRow = 10
    For lngCol = 2 To 32 'your date column
    With Sheets("Feb 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    'March
    lngRow = 14
    For lngCol = 2 To 32 'your date column
    With Sheets("Mar 06")
    Cells(lngRow, lngCol) = Application.WorksheetFunction.VLookup(strName, .Range("A2:AF5"), lngCol, False)
    End With
    Next

    End Select
    End Sub

    Sub ClearPreviousData()

    Dim lngRow As Long

    For lngRow = 6 To 50 Step 4
    Range(Cells(lngRow, 2), Cells(lngRow, 32)).ClearContents
    Next

    End Sub

  3. #3
    Rick Hansen
    Guest

    Re: Need loop structure to get round limited IF statements

    Hello karambos, I believe I have a solutition for programing problem. email
    me at rlhansen73@yahoo.com . Sorry, I'm new to newsgroups. R Hansen.
    somewhere in Alaska


    "karambos" <karambos.1udk6d_1125065108.0337@excelforum-nospam.com> wrote in
    message news:karambos.1udk6d_1125065108.0337@excelforum-nospam.com...
    >
    > I'm completely stuck. I have a spreadsheet that has a worksheet for
    > each month of the year plus an extra one called TOTALS.
    >
    > In each month there is one row per employee (there are about 20-30
    > employees) and the columns represent the days of the month. Its a
    > spreadsheet to calculate holiday totals.
    >
    > The idea is: the holdays get entered with a '1' and in the TOTALS list
    > there's a drop down of the names of the employees. The select their
    > name and the total appear for each month. I've tried nested IF's but
    > I'M limited to 7. I don'T have much programming knowlege and I can'T
    > figure out how to solve this problem.
    >
    > I attach a zip file containing my example
    >
    > I'm grateful for any help.
    >
    >
    > +-------------------------------------------------------------------+
    > |Filename: holiday.zip |
    > |Download: http://www.excelforum.com/attachment.php?postid=3749 |
    > +-------------------------------------------------------------------+
    >
    > --
    > karambos
    > ------------------------------------------------------------------------
    > karambos's Profile:

    http://www.excelforum.com/member.php...o&userid=16262
    > View this thread: http://www.excelforum.com/showthread...hreadid=399447
    >




  4. #4
    Registered User
    Join Date
    08-11-2005
    Location
    Netherlands Waddinxveen
    Posts
    81
    You should not use Excel for this but Access.
    1 your data can not be protected good enough (privacy)
    2 your using a sheet (for calculating) to store Data
    3 in Access you can store up to ten years of holidays easily

    info@driesign.nl

  5. #5
    Registered User
    Join Date
    11-09-2004
    Posts
    11

    Done

    thankyou all for your efforts.

    However, I found that this worked exactly as I wanted it to: call all names in January 'JanNames' and call all the data JanData. Then use this formula:

    =INDEX(JanData;MATCH(Dropdown;JanNames;0);COLUMN()-1)

    to select the row of data you need. I include the finished product
    Attached Files Attached Files

  6. #6
    Rick Hansen
    Guest

    Re: Need loop structure to get round limited IF statements

    Hello Karambos- Rick here in alaska. Here is some vba code you can try that
    will get you the same results as formula's on the excel worksheets. I also
    have a complete excel spreadsheet with this code using your examples.

    Rick rlhansen73@yahoo.com
    HTH

    Sub FindHolidayData2()

    Dim wsk As Worksheet
    Dim wsAr As Variant
    Dim EmpName As String
    Dim x As Integer, TotRow As Integer
    Dim iRow As Integer

    ' Array list of month sheets
    wsAr = Array("Jan 06", "Feb 06", "Mar 06", "Apr 06", "May 06", "Jun 06", _
    "Jul 06", "Aug 06", "Sep 06", "Oct 06", "Nov 06", "Dec 06")


    Application.ScreenUpdating = False ' freeze screen update

    Range("ClearTotals").ClearContents ' clear all data on "Totals" sheet
    EmpName = Range("DropDown") ' get employee name from dropdown
    list

    If EmpName = "" Then
    MsgBox ("Please select employee name" & vbCrLf _
    & " and try again")
    Exit Sub
    End If

    TotRow = 6 ' Totals month row pointer

    For x = 0 To 11 ' loop thru each month sheet
    Set wsk = Worksheets(wsAr(x)) ' set worksheet object pointer

    ' find row number that employee name is on
    iRow = WorksheetFunction.Match(EmpName, Range("Names"), 0) + 1
    ' copy employee holiday data from selected sheet
    wsk.Range("B" & CStr(iRow) & ":" & "AF" & CStr(iRow)).Copy
    ' paste employee monthly data in "Totals" sheet
    Range("B" & CStr(TotRow) & ":" & "AF" & CStr(TotRow)).PasteSpecial
    (xlPasteValues)
    TotRow = TotRow + 4 ' point to next month row in "Totals"
    Next x
    Application.CutCopyMode = False 'Clear the clipbrd

    End Sub


    "karambos" <karambos.1uj46g_1125324310.1418@excelforum-nospam.com> wrote in
    message news:karambos.1uj46g_1125324310.1418@excelforum-nospam.com...
    >
    > thankyou all for your efforts.
    >
    > However, I found that this worked exactly as I wanted it to: call all
    > names in January 'JanNames' and call all the data JanData. Then use
    > this formula:
    >
    > =INDEX(JanData;MATCH(Dropdown;JanNames;0);COLUMN()-1)
    >
    > to select the row of data you need. I include the finished product
    >
    >
    > +-------------------------------------------------------------------+
    > |Filename: holiday2.zip |
    > |Download: http://www.excelforum.com/attachment.php?postid=3758 |
    > +-------------------------------------------------------------------+
    >
    > --
    > karambos
    > ------------------------------------------------------------------------
    > karambos's Profile:

    http://www.excelforum.com/member.php...o&userid=16262
    > View this thread: http://www.excelforum.com/showthread...hreadid=399447
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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