|
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. +-------------------------------------------------------------------+ |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 |
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 -- hideki ------------------------------------------------------------------------ hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903 View this thread: http://www.excelforum.com/showthread...hreadid=399447 |
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 -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182 View this thread: http://www.excelforum.com/showthread...hreadid=399447 |
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 |
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 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com