VBA Error handling

[gsrahn91]

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

[vanhunk]

@Geoff:
1) Post your code as
code here
2) When attaching/saving your file, it must be as an xlsm file, otherwise you will lose the code (which is what happened). Please do so now.
3) Step through your code using F8 to see what the sequence of execution is. This way you will probably find the problem yourself.

Keep on working on it, it is the only way to learn.

Regards
vanhunk

[gsrahn91]

Vanhunk

I intentionally saved it as a regular excel. I have reattached as a macro enabled though. The file attachment is just a reference. I can't actually attach the real file, but this file will do. The code should find and replace the date in quotes, and stepping through it sometimes it works, but eventually it stops going to the resume. It sees resume skips over and goes to the second error statement causing an exit sub. I'm not sure why it's treating the resume as an error. I know you need the resume to exit the error handling and restart (as far as I understand it, I could be wrong).

Thank you.

[vanhunk]

@Geoff:
It would help if I understand what the code is supposed to do. What is supposed to happen?

Regards

[gsrahn91]

Vanhunk,

on each sheet there are several dates. This code finds the dates that need to be replaced and then replaces them with a new date. Then goes to the next sheet and does the same process again. until eventually it reaches the last sheet.

In the test file, it's finding the date 8/19 (august 19th) and replacing it with "8/19 00:00"

Regards Geoff

[Fluff]

If you comment out the "On Error Goto" lines & step through the code using F8, you should see what is going wrong.

[vanhunk]

@Geoff:

Try the code below. What it does is look through the used range of every sheet, add all instances of the date "19 August 2018", regardless of format, to a range named rng. It then replaces all of them at once with only the date portion of the entries before moving to the next sheet to perform the same. I found it to be quite quick. I attached a test file for your convenience.

Regards,
vanhunk


Sub IterateCells2()
Dim ws As Worksheet
Dim Cell As Range
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets
For Each Cell In ws.UsedRange.Cells

If IsDate(Cell.Value) And DateValue(Cell) = CDate("19 August 2018") Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)

End If

End If

Next 'Next cell

On Error Resume Next
rng.Value = CDate("19 August 2018")
Set rng = Nothing
On Error GoTo 0

Application.Goto Range("A1")

Next 'Next Worksheet

End Sub

[vanhunk]

@Geoff:
The "and" causes an error in above code. I corrected it and thoroughly tested it in the code below:

Sub RemoveTimeFromDate()
Dim ws As Worksheet
Dim Cell As Range
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets
For Each Cell In ws.UsedRange.Cells

If IsDate(Cell.Value) Then
If DateValue(Cell) = CDate("19 August 2018") Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
End If

Next 'Next cell

On Error Resume Next
rng.Value = CDate("19 August 2018")
Set rng = Nothing
On Error GoTo 0

Application.Goto Range("A1")

Next 'Next Worksheet

End Sub