- Joined
- Oct 16, 2012
- Messages
- 1
- Reaction score
- 0
Hi guys, how r u? Hope you're fine!
Today, i've been trying to keep my sub running even if errors are encountered along the way. Even though, On Error Resume Next does not work (it only allows the sub to keep running) and it makes the variable to assume a value and paste it to the sheet even when errors are encountered.
What is making it work with errors is WorksheetFunction.Match that returns #N/A depending on the data read. When this occurs, my currently macro continues to the next line, pasting data to cells and i didn't want this to happen! Please, consider having a look at my code right below:
Sub PasteTasks()
Application.ScreenUpdating = False 'doesn't allow screen to update (user can't see the program running)
Dim LastRow As Integer 'variables
Dim TaskIndex, FindToday As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row - 1 'define loop last row
On Error Resume Next
For i = 10 To LastRow 'loop to copy/paste data depending on the task
With Sheet9 'copy content depending on task
.Activate
.Cells(i, 2).Copy
task = .Cells(i, 1)
End With
With Plan13 'takes into consideration current date and paste content to the defined task
.Activate
TaskIndex = Application.WorksheetFunction.Match(task, .Range("B1:CM1"), 0) + 1
FindToday = Application.WorksheetFunction.Match(CLng(Date), .Range("A2:A214"), 0) + 1
Cells(FindToday, TaskIndex).PasteSpecial Paste:=xlPasteValues
End With
Next
Application.CutCopyMode = False 'cancel paste history
Worksheets("Tasks").Activate 'back to task
End Sub
Today, i've been trying to keep my sub running even if errors are encountered along the way. Even though, On Error Resume Next does not work (it only allows the sub to keep running) and it makes the variable to assume a value and paste it to the sheet even when errors are encountered.
What is making it work with errors is WorksheetFunction.Match that returns #N/A depending on the data read. When this occurs, my currently macro continues to the next line, pasting data to cells and i didn't want this to happen! Please, consider having a look at my code right below:
Sub PasteTasks()
Application.ScreenUpdating = False 'doesn't allow screen to update (user can't see the program running)
Dim LastRow As Integer 'variables
Dim TaskIndex, FindToday As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row - 1 'define loop last row
On Error Resume Next
For i = 10 To LastRow 'loop to copy/paste data depending on the task
With Sheet9 'copy content depending on task
.Activate
.Cells(i, 2).Copy
task = .Cells(i, 1)
End With
With Plan13 'takes into consideration current date and paste content to the defined task
.Activate
TaskIndex = Application.WorksheetFunction.Match(task, .Range("B1:CM1"), 0) + 1
FindToday = Application.WorksheetFunction.Match(CLng(Date), .Range("A2:A214"), 0) + 1
Cells(FindToday, TaskIndex).PasteSpecial Paste:=xlPasteValues
End With
Next
Application.CutCopyMode = False 'cancel paste history
Worksheets("Tasks").Activate 'back to task
End Sub