gsrahn91
08-19-2018, 08:59 AM
The code I'm writing worked the first time I put it in, but now it does not. I'm not sure what happened. I'm a beginner at writing any kind of code so any help would be apperciated. basically now it just runs through the whole code (while and wend) and then at the end instead of starting back at start over it goes to exit sub.
Sub SetTheTime()
'ErrorHandlingStatement
On Error GoTo firsterror
'begining of resume
Startover:
'selecting range a2
Range("A2").Select
'finds the cell you are searching for to replace
Cells.Find(what:="8/19", after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
'what needs to be looped
While ActiveCell.Value > 43331 'this is equal to the day in the above formula
'this will clear the cell
ActiveCell.ClearContents
'this will place the correct date and time in
ActiveCell.Value = "8/19 00:00"
'finds the cell you are searching for to replace
Cells.Find(what:="8/19", after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
'end of loop
Wend
'this will move to the next sheet
ActiveSheet.Next.Select
'second error message
On Error GoTo completed
'first error start
firsterror:
'this will move to the next sheet
ActiveSheet.Next.Select
'startover statment to exit error
Resume Startover
'second error exit sub
completed:
Exit Sub
End Sub
any help is greatly apperciated
Thanks Geoff
Sub SetTheTime()
'ErrorHandlingStatement
On Error GoTo firsterror
'begining of resume
Startover:
'selecting range a2
Range("A2").Select
'finds the cell you are searching for to replace
Cells.Find(what:="8/19", after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
'what needs to be looped
While ActiveCell.Value > 43331 'this is equal to the day in the above formula
'this will clear the cell
ActiveCell.ClearContents
'this will place the correct date and time in
ActiveCell.Value = "8/19 00:00"
'finds the cell you are searching for to replace
Cells.Find(what:="8/19", after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
'end of loop
Wend
'this will move to the next sheet
ActiveSheet.Next.Select
'second error message
On Error GoTo completed
'first error start
firsterror:
'this will move to the next sheet
ActiveSheet.Next.Select
'startover statment to exit error
Resume Startover
'second error exit sub
completed:
Exit Sub
End Sub
any help is greatly apperciated
Thanks Geoff