Run-time error '1004'

[leeksleeks]

Hi,

I am running a macro for a results based spreadsheet and the data it imports sometimes appears on different rows. This then trips up my macro and I get the error message 'Run time error 1004: No data was selected to parse. The problem occurs when it references "Row 70" in all the lines of the below code as sometimes the information it is trying to use is in "Row 69". Below is the part of my code where you can see how many time row 70 is used. Is there any way if there is an error for it to use row 69 instead?

Range("A70").Select
Selection.TextToColumns Destination:=Range("A70"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("B70").Select
Selection.TextToColumns Destination:=Range("B70"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("C70").Select
Selection.TextToColumns Destination:=Range("C70"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("D70").Select
Selection.TextToColumns Destination:=Range("D70"), DataType:=xlFixedWidth, _
OtherChar:=")", FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(17 _
, 1), Array(23, 1), Array(27, 1), Array(32, 1)), TrailingMinusNumbers:=True
Range("E70:J70").Select
Selection.ClearContents
Range("B70:D70").Select
Selection.Cut Destination:=Range("D70:F70")
Range("B70:F70").Select
Selection.Cut Destination:=Range("D70:H70")
Range("A70").Select
Selection.TextToColumns Destination:=Range("A70"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("A70").Select
Selection.ClearContents
Range("B70").Select
Selection.Cut Destination:=Range("A70")
Range("F70").Select
Selection.Cut Destination:=Range("B70")
Range("G70").Select
Selection.Cut Destination:=Range("C70")
Range("H70").Select
Selection.Cut Destination:=Range("D70")

I look forward to hearing if there is a possible solution to this problem.

Cheers

 

[oldbrewer]

USE IF(ISERROR..... on A70 and "jump" range("a70").select and go to new line range("A69").select

 

[leeksleeks]

Thanks for the quick reply. So how would that look written out in the above code?

Cheers

 

[Eric Golf]

Hi,

Easier if you have some way of identifying which row the macro should act on and store it in a variable e.g. MyRow

Ranges can then be acted on by combining column with the variable e.g.

Range("A" & MyRow).
Range("E" & MyRow & ":J" & MyRow).

In addition there is generally no need to Select cells, you can combine most .Select statements with the Selection. statement e.g.

Range("A70").Select
Selection.ClearContents

becomes

Range("A" & MyRow).ClearContents

Hope this helps,

Eric

 

[leeksleeks]

Thanks Eric. So if I was to rewrite the code with yours, how would it look? I run this macro on thousands of worksheets one after the other and some of them are row 70 and some are row 69. I basically need to know where to insert an error message and where to define MyRow.

Cheers

 

[leeksleeks]

I have managed to re-write the code so that it can't have this problem. A bit long winded but seems to work. Thanks everyone for their contributions.

Cheers