Runtime error 1004

**fieldsy73** · 02-23-2007, 07:18 AM

Help!

I'm trying to run a macro created by an ex-employee which has today stopped working. Unfortunately, i know virtually nothing about coding so any help would be greatly appreciated.

The macro I'm running creates the error messgae 1004. I've highlighted the line of text which it appears to be having problems with.

' finds bottom most cell in selection, this cell contains the number of records in the import
' and names this number Jeff2
Cells(50, 2).Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, -1).Select
Selection.TextToColumns Destination:=Cells(1, 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True, Other:=True, _
OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Jeff2 = Cells(1, 1).Value
Cells(1, 1).Clear

For rwindex = 1 To 100
If Cells(rwindex, 1).Value Like "name*" Then Cells(rwindex, 1).EntireRow.ClearContents
Next rwindex

Thanks for your help.
John

**antoka05** · 02-23-2007, 07:41 AM

The problem may be you extend selection with end down but if you arrive at the last row the next code offset (2,1) can't go other 2 rows down.

If the problem isn't what I think try so:
change the code:

Please Login or Register  to view this content.

with the code:

Please Login or Register  to view this content.

I hope you can resolve your problem.

Regards,
Antonio

**fieldsy73** · 02-23-2007, 07:59 AM

Hi Antonio,

Many thanks for the prompt reply.

I've tried replacing the problem code with your suggested code and it appears to get around my current problem. However, it then highlights the next line of code:

Selection.TextToColumns Destination:=Cells(1, 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True, Other:=True, _
OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Jeff2 = Cells(1, 1).Value
Cells(1, 1).Clear

I also tried changing the row to 0 which also resolved my initial problem but then highlighted the same problem above.

Many Thanks

John