C
crj
I am trying to build a table that lists projects and hours worked for that
project for each day of the week.
I start by doing a find on my timesheet sheet for a certain date if I find
an entry for that date I then want to do a find on my project number column
on the display sheet to see if that project already has an entry (if it
does I want to add more hours for that date). I have this working ok my
problem is that when I finish checking for the project number I exit out of
that loop and fall back into the loop looking for the date and this find next
is trying match on the project number instead of the date.
Here is the code example:
With timesheetRange
Set C = .Find(datetofind, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
rowfound = C.Row
AlreadyUpdated = False
projectToUpdate = Worksheets("Timesheet").Range("C" &
rowfound).Value
With Worksheets("ProjectDB").Range("B22:C" & pdbLR)
Set pdbC = .Find(projectToUpdate, LookIn:=xlValues)
If Not pdbC Is Nothing Then
pdbfirstAddress = pdbC.Address
Do
pdbrowfound = pdbC.Row
'Select
If Worksheets("ProjectDB").Range("C" & pdbrowfound).Value
= Worksheets("Timesheet").Range("E" & rowfound).Value Then
'add Hours
Worksheets("ProjectDB").Range("E" & pdbrowfound).Value
= Worksheets("Timesheet").Range("D" & rowfound).Value
AlreadyUpdated = True
End If
Set pdbC = .FindNext(pdbC)
Loop While Not pdbC Is Nothing And pdbC.Address <>
pdbfirstAddress
End If
End With
‘ If we haven’t updated then we must need to add a line
If Not AlreadyUpdated Then
'Project #
Worksheets("projectdb").Range("B" & pdbNextline).Value =
Worksheets("timesheet").Range("C" & rowfound).Value
'Activity
Worksheets("projectdb").Range("C" & pdbNextline).Value =
Worksheets("timesheet").Range("E" & rowfound).Value
'Hours
Worksheets("projectdb").Range("D" & pdbNextline).Value =
Worksheets("timesheet").Range("D" & rowfound).Value
pdbNextline = pdbNextline + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
project for each day of the week.
I start by doing a find on my timesheet sheet for a certain date if I find
an entry for that date I then want to do a find on my project number column
on the display sheet to see if that project already has an entry (if it
does I want to add more hours for that date). I have this working ok my
problem is that when I finish checking for the project number I exit out of
that loop and fall back into the loop looking for the date and this find next
is trying match on the project number instead of the date.
Here is the code example:
With timesheetRange
Set C = .Find(datetofind, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
rowfound = C.Row
AlreadyUpdated = False
projectToUpdate = Worksheets("Timesheet").Range("C" &
rowfound).Value
With Worksheets("ProjectDB").Range("B22:C" & pdbLR)
Set pdbC = .Find(projectToUpdate, LookIn:=xlValues)
If Not pdbC Is Nothing Then
pdbfirstAddress = pdbC.Address
Do
pdbrowfound = pdbC.Row
'Select
If Worksheets("ProjectDB").Range("C" & pdbrowfound).Value
= Worksheets("Timesheet").Range("E" & rowfound).Value Then
'add Hours
Worksheets("ProjectDB").Range("E" & pdbrowfound).Value
= Worksheets("Timesheet").Range("D" & rowfound).Value
AlreadyUpdated = True
End If
Set pdbC = .FindNext(pdbC)
Loop While Not pdbC Is Nothing And pdbC.Address <>
pdbfirstAddress
End If
End With
‘ If we haven’t updated then we must need to add a line
If Not AlreadyUpdated Then
'Project #
Worksheets("projectdb").Range("B" & pdbNextline).Value =
Worksheets("timesheet").Range("C" & rowfound).Value
'Activity
Worksheets("projectdb").Range("C" & pdbNextline).Value =
Worksheets("timesheet").Range("E" & rowfound).Value
'Hours
Worksheets("projectdb").Range("D" & pdbNextline).Value =
Worksheets("timesheet").Range("D" & rowfound).Value
pdbNextline = pdbNextline + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With