Pasting and Deleting Error in Excel Macro

[Arthur1]

Hi,

When I run the code below for some reason I keep getting an Excel error and Excel crashes? It happens before I delete some rows. I have noted below The point at which Excel crashes. Any help would be appreciated.



Public ProcessFile
Public AppDir$
Public old As Variant
Public current As Variant
Public myWB$
Public r As Long
Public DataRow As Long
Public CS_PB As String
Public Name As String


Sub PasteSpecial()
'ctrl-l shortcut macro

'paste formulas as values
Range(Cells(3, 3), Cells(Range("C3&quot.End(xlDown).Row, 4)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C3&quot.Select

'format column
Columns("D&quot.Select
Selection.NumberFormat = "#,##0"
Columns("E:E&quot.Select
Selection.NumberFormat = "General"
Range("C3&quot.Select

'add formula for cap
Range(Cells(3, 5), Cells(Range(&quot;A3&quot.End(xlDown).Row, 5)).FormulaR1C1 = &quot;=IF(RC[-1]=&quot;&quot;&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]=&quot;&quot;#N/A N.A.&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]=&quot;&quot;#N/A Sec&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]>=2500000000,&quot;&quot;L&quot;&quot;,IF(AND(RC[-1]>=0,RC[-1]<2500000000),&quot;&quot;S&quot;&quot;,&quot;&quot;?&quot;&quot))))&quot;

'get sheet ready to loop through to change the date to character
Columns(&quot;H:H&quot.Select
Selection.Insert Shift:=xlToRight

'loop through cells to update data
r = 3

Do Until Cells(r, 9).Value = &quot;&quot;
If Cells(r, 9).Value <> &quot;&quot; Then
Cells(r, 1).Value = Format(Cells(r, 1), &quot;>&quot
Cells(r, 4).Select
ActiveCell.FormulaR1C1 = Cells(r, 4).Value
Cells(r, 8).Select
ActiveCell.FormulaR1C1 = _
&quot;=TEXT(YEAR(RC[-1]),&quot;&quot;0000&quot;&quot& TEXT(MONTH(RC[-1]),&quot;&quot;00&quot;&quot& TEXT(DAY(RC[-1]),&quot;&quot;00&quot;&quot&quot;

End If
r = r + 1
Loop

Columns(&quot;H:H&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;H1&quot.Value = &quot;Trade Date&quot;

ActiveWorkbook.Save

Deleting

End Sub


***********************HERE**********************
Sub Deleting()
Columns(&quot;B:H&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Rows(&quot;2:2&quot.Select
Selection.Delete Shift:=xlUp

Columns(&quot;I:I&quot.Select
Selection.Delete Shift:=xlToLeft
Range(&quot;A1&quot.Select
End Sub

 

[IfThenElvis]

Note: This question is also posted with replies in the Microsoft Office Forum.