Slicemahn
12-14-2007, 04:15 AM
Hello Everyone
I am trying to purge excess info within cells. I have written a macro that gives me a 400 error each time.
There are about 30,000 rows in which I have to clean. The content of each cell is the following:
SPRATT, JACK - 12345 (35972)
My goal is to have
JACK.SPRATT 35972 preferably with the name in one column and the ID in another.
Here is the code I wrote that returns the error
Sub DeleteExcess()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim NewString As String
Dim FindDash As Integer
EndRow = Cells(65536, 1).End(xlUp).Row
For RowLoop = EndRow To 1 Step -1
FindDash = Application.WorksheetFunction.Find("-", Cells(RowLoop, 1))
StringChecker = Len(Cells(RowLoop, 1))
NewString = Left(Cells(RowLoop, 1), (StringChecker - FindDash))
Cells(RowLoop, 1).Value = NewString
Next RowLoop
End Sub
I look forward to any thoughts or suggestions on how to conquer this problems. Many thanks.
I am trying to purge excess info within cells. I have written a macro that gives me a 400 error each time.
There are about 30,000 rows in which I have to clean. The content of each cell is the following:
SPRATT, JACK - 12345 (35972)
My goal is to have
JACK.SPRATT 35972 preferably with the name in one column and the ID in another.
Here is the code I wrote that returns the error
Sub DeleteExcess()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim NewString As String
Dim FindDash As Integer
EndRow = Cells(65536, 1).End(xlUp).Row
For RowLoop = EndRow To 1 Step -1
FindDash = Application.WorksheetFunction.Find("-", Cells(RowLoop, 1))
StringChecker = Len(Cells(RowLoop, 1))
NewString = Left(Cells(RowLoop, 1), (StringChecker - FindDash))
Cells(RowLoop, 1).Value = NewString
Next RowLoop
End Sub
I look forward to any thoughts or suggestions on how to conquer this problems. Many thanks.