Error handling? Loop? IF? problems 2

[stillwillyboy]

The following code works on my PC at home, but when I sent it to work to run, it keeps jumping to the error handling message. I have verified that the file does exist in the directory. When I step thru it (F8), it opens up the PRVY6EPI.csv file for corrections, steps thru the procedure, then executes “handler” without correcting any of the numbers. Thoughts? TIA, Bill

Sub CorrectClientNumberBeforeSendingToADP()
'this sub changes selected client numbers from 4 to 5 digits Application.ScreenUpdating = False Application.DisplayAlerts = False Application.StatusBar = "Changing Client Numbers"
Dim cell As Range
On Error GoTo handler
Workbooks.OpenText FileName:= _
"C:\ClientPayOutsource\Prvy6epi\PRVY6EPI.csv"
'Windows("PRVY6EPI.CSV").Activate
‘Range("C1").Select

Range("C65536").End(xlUp).Select

For Each cell In Selection
If cell.Value = 1583 Then 'correcting first employee
cell.Value = "15831"
Else
cell.Value = cell.Value
End If
If cell.Value = 1628 Then 'correcting second employee
cell.Value = "16281"
Else
cell.Value = cell.Value
End If
If cell.Value = 1655 Then 'correcting third employee
cell.Value = "16555"
Else
cell.Value = cell.Value
End If
If cell.Value = 2064 Then 'correcting fourth employee
cell.Value = "20642"
Else
cell.Value = cell.Value
End If
Next cell
Range("A1").Select
ActiveWorkbook.SaveAs FileName:= _
"C:\Program Files\Microsoft Office\Excel\OutsourceXfer\PRVY6EPI.CSV"
ActiveWorkbook.Close
Exit Sub
handler: MsgBox "The PRVY6EPI.CSV file does not currently exist." _
& "Most likely it was deleted from its folder. Reprocessing " _
& "will create the file. "

Application.ScreenUpdating = True
End Sub

 

[SkipVought]

...and as you step thru the procedure, what is the LAST STATEMENT that is processed before it branches to the error handler?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

Basically you are ONLY SELECTING ONE CELL: the LAST non-empty cell in column C.

What is your intention: to loop thru the cells from C1 to the last cell in column C?

  For each cell in range(cells(1,"C"), Cells(Range("C65536").End(xlUp).row, "C"))

  Next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

Hi Bill,

Got to guess a bit here.

This code ..

Range("C65536").End(xlUp).Select

For Each cell In Selection
    :
    :
Next cell
Range("A1").Select
ActiveWorkbook.SaveAs FileName:= _
      "C:\Program Files\Microsoft Office\Excel\OutsourceXfer\PRVY6EPI.CSV"

.. checks a single cell so, the For .. Next happens once and may or may not change a single cell; it then tries to save the workbook. Does the folder you're trying to save into exist on your work machine?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[stillwillyboy]

Skip/Tony,

As always, you guys are great! I put in Skip’s line (below), added quotes to my four digit numbers and then discovered that I had not changed the Save directory. Duh! It worked when I called in from home to work. We’ll see how she works come Monday morning. Thanks!

For each cell in range(cells(1,"C"), Cells(Range("C65536").End(xlUp).row, "C"))