Need loop structure to get round limited IF statements

[karambos]

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

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

The idea is: the holdays get entered with a '1' and in the TOTALS lis
there's a drop down of the names of the employees. The select thei
name and the total appear for each month. I've tried nested IF's bu
I'M limited to 7. I don'T have much programming knowlege and I can'
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
+-------------------------------------------------------------------

 

[hideki]

If you want to code in VBA, you can use select case rather than If. Her
is a sample code. But like to want you that I'm beginner myself. Jus
like to try some coding.

To see the result, put one command button in your "totals" sheet. The
execute below procedure with that button. This only work for the firs
two person and until Mac only. You can add if you think okey. But
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 Su

 

[Rick Hansen]

Hello karambos, I believe I have a solutition for programing problem. email
me at (e-mail address removed) . Sorry, I'm new to newsgroups. R Hansen.
somewhere in Alaska

 

[Dnereb]

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

(e-mail address removed)

 

[karambos]

thankyou all for your efforts.

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

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

to select the row of data you need. I include the finished produc

+-------------------------------------------------------------------
|Filename: holiday2.zip
|Download: http://www.excelforum.com/attachment.php?postid=3758
+-------------------------------------------------------------------

 

[Rick Hansen]

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 (e-mail address removed)
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