| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested find/find next
I am trying to find all ted for timesheet records for a certain date. Once I
find a record I need to do another find to see if I have a display line already created for that project number. If I do I want to update it rather than create a new lne. My problem is that when I go to my next date the findnext looks for project rather than date (find next runs against last find rather than the first find) here is the code: Application.Goto Sheets("Timesheet").Range("B2") LR = Cells(Rows.Count, 2).End(xlUp).Row Set tsRange = Worksheets("Timesheet").Range("B2:E" & LR) Set refRange = Worksheets("Reference").Range("C5") Set pdbRange = Worksheets("ProjectDB").Range("B:J") Application.Goto Sheets("ProjectDB").Range("B22") pdbLR = Cells(Rows.Count, 2).End(xlUp).Row If pdbLR 21 Then Range("B22:J" & pdbLR).Select Selection.ClearContents End If Range("B22:B" & pdbLR).Select Selection.NumberFormat = "0000000000" pdbNextline = 22 For i = 1 To 7 If i = 1 Then datetofind = CDate(refRange.Value - 6) If i = 2 Then datetofind = CDate(refRange.Value - 5) If i = 3 Then datetofind = CDate(refRange.Value - 4) If i = 4 Then datetofind = CDate(refRange.Value - 3) If i = 5 Then datetofind = CDate(refRange.Value - 2) If i = 6 Then datetofind = CDate(refRange.Value - 1) If i = 7 Then datetofind = CDate(refRange.Value) With tsRange Set c = .Find(datetofind, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do rowfound = c.Row If i = 1 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 If i = 2 Then Application.Goto Sheets("ProjectDB").Range("B22") pdbLR = Cells(Rows.Count, 2).End(xlUp).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) If pdbc Is Nothing Then Exit Do If pdbc.Address = pdbfirstAddress Then Exit Do Loop End If End With 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("E" & pdbNextline).Value = Worksheets("timesheet").Range("D" & rowfound).Value pdbNextline = pdbNextline + 1 End If End If 'i=2 If i = 3 Then End If 'i=3 If i = 4 Then End If 'i=4 If i = 5 Then End If 'i=5 If i = 6 Then End If 'i=6 If i = 7 Then End If 'i=7 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < FirstAddress End If End With Next i |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
nested find/find next
See Find Method here;
http://www.ozgrid.com/VBA/find-method.htm and scroll down to "Find_Bold_Cat" If your dates are NOT US format, look here as well; http://www.ozgrid.com/VBA/find-dates.htm -- Regards Dave Hawley www.ozgrid.com "crj" wrote in message ... I am trying to find all ted for timesheet records for a certain date. Once I find a record I need to do another find to see if I have a display line already created for that project number. If I do I want to update it rather than create a new lne. My problem is that when I go to my next date the findnext looks for project rather than date (find next runs against last find rather than the first find) here is the code: Application.Goto Sheets("Timesheet").Range("B2") LR = Cells(Rows.Count, 2).End(xlUp).Row Set tsRange = Worksheets("Timesheet").Range("B2:E" & LR) Set refRange = Worksheets("Reference").Range("C5") Set pdbRange = Worksheets("ProjectDB").Range("B:J") Application.Goto Sheets("ProjectDB").Range("B22") pdbLR = Cells(Rows.Count, 2).End(xlUp).Row If pdbLR 21 Then Range("B22:J" & pdbLR).Select Selection.ClearContents End If Range("B22:B" & pdbLR).Select Selection.NumberFormat = "0000000000" pdbNextline = 22 For i = 1 To 7 If i = 1 Then datetofind = CDate(refRange.Value - 6) If i = 2 Then datetofind = CDate(refRange.Value - 5) If i = 3 Then datetofind = CDate(refRange.Value - 4) If i = 4 Then datetofind = CDate(refRange.Value - 3) If i = 5 Then datetofind = CDate(refRange.Value - 2) If i = 6 Then datetofind = CDate(refRange.Value - 1) If i = 7 Then datetofind = CDate(refRange.Value) With tsRange Set c = .Find(datetofind, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do rowfound = c.Row If i = 1 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 If i = 2 Then Application.Goto Sheets("ProjectDB").Range("B22") pdbLR = Cells(Rows.Count, 2).End(xlUp).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) If pdbc Is Nothing Then Exit Do If pdbc.Address = pdbfirstAddress Then Exit Do Loop End If End With 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("E" & pdbNextline).Value = Worksheets("timesheet").Range("D" & rowfound).Value pdbNextline = pdbNextline + 1 End If End If 'i=2 If i = 3 Then End If 'i=3 If i = 4 Then End If 'i=4 If i = 5 Then End If 'i=5 If i = 6 Then End If 'i=6 If i = 7 Then End If 'i=7 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < FirstAddress End If End With Next i |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested MID and FIND - OR? | Excel Worksheet Functions | |||
| Use of Find with Left, Mid, Right functions in nested IF(and('s | Excel Discussion (Misc queries) | |||
| Nested IF, reports incorrectly, can't find anything like it to ref | Excel Worksheet Functions | |||
| Nested function 'FIND(OR)' | Excel Programming | |||
| Nested Find problem | Excel Programming | |||
Linear Mode
