VBA Solution for Index/Match

LikeButtah

LikeButtah

Board Regular
Joined
Jun 24, 2011
Messages
168
Hello All,

This is our payroll sheet that we paste work hours into from a roll call workbook.


Excel 2012
ABCDEFGHIJ
Rate
Regular Hrs
5% Regular
10% Regular
Overtime Hrs
5% Overtime
10% Overtime
Disp/Dr Regular Hrs
Disp/Dr 5% Reg
Disp/Dr 10% Reg
Disp/Dr Overtime Hrs
Disp/Dr 5% OT
Disp/Dr 10% OT
Personal/Sick
Holiday
Vacation
Sp. Vacation
Death in Family
Jury Duty

<tbody>
[TD="align: center"]8[/TD]
[TD="align: right"]Date[/TD]
[TD="align: center"]20-Jul[/TD]
[TD="align: center"]21-Jul[/TD]
[TD="align: center"]22-Jul[/TD]
[TD="align: center"]23-Jul[/TD]
[TD="align: center"]24-Jul[/TD]
[TD="align: center"]25-Jul[/TD]
[TD="align: center"]26-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]Mon[/TD]
[TD="align: center"]Tues[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thurs[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]TOTAL[/TD]
[TD="align: center"]COST[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99"][/TD]
[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="align: right"] 40.00[/TD]
[TD="align: right"] 778.80[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]21[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]22[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]23[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]24[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]25[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]26[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]27[/TD]

[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]TOTAL[/TD]
[TD="align: right"] 8.00[/TD]
[TD="align: right"] 8.00[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 8.00[/TD]
[TD="align: right"] 8.00[/TD]
[TD="align: right"] 8.00[/TD]
[TD="align: right"] 40.00[/TD]
[TD="align: right"] 778.80[/TD]

</tbody>
Brooks

That works just fine but I need a VBA Index/Match solution to take the below info from the roll call workbook and if the day type picked in Column C is "Personal Day" I need the 8 hours from Column G it to go into Row 22, "SVD" into Row 25 and "Vacation Day" into Row 24

Excel 2012
CDEFG
PERSONAL DAY
SVD
VACATION DAY

<tbody>
[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Leave Granted[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]38[/TD]

[TD="bgcolor: #FFFFCC, align: center"]BROOKS[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8:00[/TD]

[TD="align: center"]39[/TD]

[TD="bgcolor: #FFFFCC, align: center"]SMITH[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8:00[/TD]

[TD="align: center"]40[/TD]

[TD="bgcolor: #FFFFCC, align: center"]BLUE[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8:00[/TD]

</tbody>
T2_SUNDAY

I am currently using this code to pull the hours worked from the roll call workbook into the payroll sheet and was hoping to modify it to also paste the Leave Time into the payroll sheet which would save a lot of time. The Range for the Leave Time runs from row 38 to 59. Thank you for taking the time to look at this for me.

Code: 
Function CalcHours(OffName As String, CalcDate As Date, PayType As String)Application.Volatile
'
' Takes OfficerName(Last,First), DayWorked, Tier
'
    Dim Hours As Double
    Dim Result As Variant
    Dim LName As String
    Dim rng As Variant
    Dim EndDate As String
    Dim CurDay As String
    Dim Tour As Integer
    Dim ResultCol As Integer
    Dim PostType As String
    
    Dim post As Variant
    
    '' Parse all the possible types of payment we might be asked to report on
    '' For many of these there's no automatic way to calculate it, so if we
    '' Don't know what it is, return immediately
    
    Select Case PayType
        Case "Regular Hrs"
            ResultCol = 8
            PostType = "reg"
        Case "5% Regular"
            ResultCol = 9
            PostType = "reg"
        Case "10% Regular"
            ResultCol = 10
            PostType = "reg"
        'Case "Disp/Dr Regular Hrs"
        '    ResultCol = 8
        'Case "Disp/Dr 5% Reg"
        '    ResultCol = 9
        'Case "Disp/Dr 10% Reg"
        '    ResultCol = 10
        Case Else
            CalcHours = ""
            Exit Function
    End Select


    Hours = 0
    
    'Format like 7-10-11
    EndDate = Format(Range("D5").Value, "m-d-yy")
    
    'Get full day of week
    CurDay = Format(CalcDate, "DDDD")
    
    'Get last name(which is given first) if multiple names are given
    LName = Split(OffName, ",")(0)


    ' Look through each of the tours to see when the officer worked that day
    For Tour = 1 To 3
        'rng = Workbooks("WeeklySchedule (" & EndDate & ").xls").Worksheets("T" & Tour & "_" & CurDay).Range("B6:L33")
        'postloc = rng.Find(what:=LName)
        'post =


        
        rng = Workbooks("WeeklySchedule (" & EndDate & ").xls").Worksheets("T" & Tour & "_" & CurDay).Range("B6:L33")


        Result = Application.VLookup(LName, rng, ResultCol, False)
        If (IsError(Result) Or WorksheetFunction.IsNA(Result)) Then
            ''' Value was not found - do nothing
        Else
            ''' Value was found, add to what we already had
            Hours = Hours + (Result * 24)
        End If
    Next Tour
    
    ' So that it prints nicely, return "" instead of 0
    If (Hours = 0) Then
        CalcHours = ""
    Else
        CalcHours = Hours
    End If
    
End Function

Just to show you what the Roll Call worksheet looks like so you can see where it's pulling the info from:
Excel 2012
ABCDEFGHIJKLM
Tour 1
RegReg+5%Reg+10%Total HrsX-Check

<tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]8/17/2015[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Week Ending:[/TD]
[TD="bgcolor: #FFFFFF, align: center"]8/23/2015[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]POST[/TD]
[TD="align: center"]OFFICER[/TD]
[TD="align: center"]RADIO #[/TD]
[TD="align: center"]MEAL[/TD]
[TD="align: center"]TIME IN[/TD]
[TD="align: center"]TIME OUT[/TD]
[TD="align: center"]HOURS WORKED[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]CLOSED[/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="align: center"]0:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]DORM[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SMITH[/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]23:00[/TD]
[TD="bgcolor: #FFFFCC, align: center"]7:30[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]0:00[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]DORM[/TD]
[TD="bgcolor: #FFFFCC, align: center"]BROOKS[/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]23:00[/TD]
[TD="bgcolor: #FFFFCC, align: center"]7:30[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]0:00[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]DORM[/TD]
[TD="bgcolor: #FFFFCC, align: center"]BLUE[/TD]
[TD="bgcolor: #FFFFCC, align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]23:00[/TD]
[TD="bgcolor: #FFFFCC, align: center"]7:30[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]0:00[/TD]

</tbody>
T1_MONDAY​
 
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
1K
Alex Blakenburg
A
R
  • Solved
Formula to show a zero when options is not there.
2
Replies
11
Views
226
Trebor76
T
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
501
myall_blues
myall_blues
R
  • Solved
Holiday without adding Time in and Time Out
Replies
3
Views
51
Peter_SSs
Peter_SSs
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
624
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top