Use Find or Match to update records?

[ColdDay]

I have two sheets. TimeSheetEntry (TSE) and CLNTINFO (CI). TSE has daily time sheet entries including leave time hours taken using the codes 186 and 189. CI has the balance of leave hours available in Col G.. I am needing to update the balance in CI from the weeks entries on TSE. The first sub below works, but if and only if the client number value from TSE happens to match the current value in Col A of CI. Not realistic. I tried using .Find in the 2nd sub below but ran into Range and Type Mismatch issues. How can get my records on CI to be updated? Thanks in advance.

Sub UpdateLeaveHours()
Application.StatusBar = "Updating Leave Time Balance"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LeaveHours As Single
Dim ClientNumber As Single

'setting CLNTINFO sheet to A1
Worksheets("CLNTINFO").Select
Range("A1").Select 'client number

Sheets("TimeSheetEntry").Select
'sort by job # which is Col B, then by client# Col A
SortByJobThenClient 'separate proc that sorts data on TimeSheetEntry sheet.
Range("B2").Select  'col B has job #'s. headers in col's.

TimeSheetEntry:  'come back for next client entry
Sheets("TimeSheetEntry").Select

Do 'should be able to us a better method than a DO loop for this proc.
With ActiveCell 'AC is B2 on TimeSheetEntry sheet
If ActiveCell.Value = 186 Or ActiveCell.Value = 189 Then  '
    LeaveHours = ActiveCell.Offset(0, 1).Value 
    ClientNumber = ActiveCell.Offset(0, -1).Value 'client # in Col A
    'info is in variables LeaveHours and ClientInfo.

    ActiveCell.Offset(1, 0).Select 'still on TimeSheetEntry sheet. _
        Moving down a row for next time thru loop
        
        'Go to CLNTINFO sheet now.
        Worksheets("CLNTINFO").Select
 
   'following works if and only if the value in ClientNumber = the value _
    in the activecell on the CLNTINFO sheet. not gonna happen in the real world.
    'Need to use Find or Match to find the correct ClientNumber on the CLNTINFO sheet?
        Do
        With ActiveCell 'A1 on CLNTINFO sheet
        If ActiveCell.Value = ClientNumber Then
        ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(0, 6).Value - LeaveHours
        End If
        ActiveCell.Offset(1, 0).Select  'still on CLNTINFO sheet
        End With
        
GoTo TimeSheetEntry

Loop Until ActiveCell.Value = ""
'Sheets("TimeSheetEntry").Select 'go back for next client with leave time
End If
ActiveCell.Offset(1, 0).Select
End With
Loop Until ActiveCell > 189 'anything higher is not leave time

Range("A1").Select

End Sub

Sub UpdateLeaveHours2()
Dim ClientForLeave As Single
Dim LeaveHours As Single
Dim ClientNumber As Single
Dim FirstAddress As Single

Sheets("TimeSheetEntry").Select
Do 'should be able to us a better method than a DO loop for this proc?
With ActiveCell 'AC is B2 on TimeSheetEntry sheet
If ActiveCell.Value = 186 Or ActiveCell.Value = 189 Then  '
    LeaveHours = ActiveCell.Offset(0, 1).Value 

    ClientNumber = ActiveCell.Offset(0, -1).Value 'client # in Col A
    'info is in variables LeaveHours and ClientInfo. Go to CLNTINFO sheet now.

Set ClientForLeave = Worksheets("CLNTINFO").Range("A:A").Find(What:=ClientNumber, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not ClientForLeave Is Nothing Then
        FirstAddress = ClientForLeave.Address
        Do 'in col A
        ClientForLeave.Value = ActiveCell.Offset(0, 6).Value - LeaveHours
            Set ClientForLeave = .FindNext(ClientForLeave)
        Loop While Not ClientForLeave Is Nothing And ClientForLeave.Address <> FirstAddress
     End If
End If
End With
Loop Until ActiveCell > 189 'On TimeSheetEntry sheet. anything higher is not leave time
End Sub

 

[SkipVought]

hi,

I'd be very VERY cautious about changing (updating) data in an Excel application like this. You'd have to be able to have code to "back out" the last set of updates and maybe other scarey scenarios, at least that's pretty scarey to me!

I think that your process would be much more safe if you were to accumulate the timesheet TRANSACTIONS each week thru the year and do an aggregation query or use an aggregation function to calculate the current time used in each or any category.

This could probably ALL be accomplished without a lick of VBA code, and I'm a big shoot-from-the-hip VBA solutions guy. If your timesheet table is properly structured, the result for any employee is likely to be a pretty simple SUMIFS() or SUMPRODUCT() function.

Please post some sample phony timesheet table data and corresponding CI table data, and I could give you an example of what I'm referring to.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ColdDay]

Hey Skip,

Below is the info on the TSE and CI sheets. I always run into problems with formatting my posts. I hope it is understandable. If not, please let me know and I’ll give it another try. Thanks.

Time Sheet Entry sheet (very abbreviated) with headers.
[pre]
A B C D E F
Number Job Hours Units Date Branch
1002 186 575 12/9/2013 1
1004 186 575 12/9/2013 3
1005 186 575 12/9/2013 2
1088 186 575 12/9/2013 1
1354 186 575 12/10/2013 2
1611 186 175 12/9/2013 3
1615 186 575 12/9/2013 3
1266 9013097 50 12/9/2013 1
1266 9013097 50 12/10/2013 1
1185 9099997 50 12/9/2013 1
1185 9099997 50 12/10/2013 1
1964 12508514 300 152 12/9/2013 3
1489 12508557 375 288 12/10/2013 3
1489 12508557 575 600 12/9/2013 3
1835 12508557 200 192 12/9/2013 3
1671 36325110 200 147 12/9/2013 1
1977 36325110 200 100 12/9/2013 1
1671 36325111 200 147 12/9/2013 1
[/pre]
I sort by job then by worker number (col A).

CLNTINFO sheet. Col G is where I was wanting to keep the leave time balance. No headers.

[pre]
A B C D E F G
1002 Ray, Stevie 0.74 2 1 7.35
1004 Charles, Ray 0.74 2 1 7.35
1005 Ziggy Pop 4.03 3 1 7.35
1017 Ringo Starr 0.84 2 1 7.35
1027 COOK, Captain 0.78 3 2 7.35
1030 JOHNSON, Lyndon 0.79 3 1 7.35
1033 Jagger, Mick 1.24 3 1 7.35
1034 Samms, Emma 0.77 3 1 7.35
1043 Goliath, DAVID A.0.80 2 1 7.35
1071 Lennon, JOHN W. 1.81 3 1 7.35
1088 POWELL, Colin 0.76 3 1 7.35
1091 Dillon, MARSHALL 0.74 3 1 7.35
[/pre]

In your reply, were you saying to have a separate leave time work sheet with every week of the year and after payroll processing, copy the leave hours over to it? Maybe using VLookUP? As opposed to just an available balance (col G) on the CI sheet? Each weeks usage would be seen and the available would be calculated and shown as below?

[pre]
A B C D E Etc>> BD BE
Number Name BegBal Week1 Week2 Etc.>> Used EndBal
1002 Ray, Stevie 36.00 12.00 0.00 12.00 24.00
1004 Charles, Ray 36.00 0.00 0.00 0.00 36.00
1005 Pop, Ziggy 36.00 0.00 0.00 0.00 36.00
1017 Starr, Ringo 36.00 6.00 0.00 6.00 30.00
1027 COOK, Captain 36.00 0.00 0.00 0.00 36.00
1030 JOHNSON, Lyndon36.00 0.00 0.00 0.00 36.00
1033 Jagger, Mick 36.00 4.00 4.00 8.00 28.00
1034 Samms, Emma 36.00 0.00 0.00 0.00 36.00
1043 Goliath,DAVID A.36.00 0.00 0.00 0.00 36.00
1071 Lennon, JOHN W. 36.00 2.00 0.00 2.00 34.00
1088 POWELL, Colin 36.00 12.00 0.00 12.00 24.00
1091 Dillon, MARSHALL36.00 6.00 5.00 11.00 25.00
[/pre]
Thanks Skip

 

[SkipVought]

I did not mean, "to have a separate leave time work sheet with every week of the year."

I meant to add each week's time data to the existing timesheet table. In that way, ALL your YTD timesheet data would exist in ONE table, from which YTD statistics can be calculated at any time, or week to week statistics can also be calculated, or monthly or quarterly. So One sheet for TSE & one sheet for CI.

So your last example, summarizing each week's statistics would be a simple matter, providing that your source data has the proper data values and date granularity. Frankly, I don't see how you would do that with values of, Number, Job, Hours, Units, Date, Branch. But maybe I'm missing something.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

BTW, out of the 18 rows of data you posted in your example, only FOUR rows correlated!!!

Please do a better job of assembling your examples of relevant data!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Furthermore, you explained NOTHIN about what columns A thru G are in your headingless table!

And how do you get 12 for Stevie Ray? No algorythm included!

What is Week1, Week2 etc? I have seen business weeks begin on Sat, or Sun or Mon. I imagine a business week could possibly begin on ANY day of the week.

You have LOTS of holes in the information that you are sharing, in order to get a tip or 2 toward a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}