Error 1004 in VBA

[NEAnthony]

Hi

I have following VBA error message 1004 run time error and at debug I have following area highlighted

Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(19, 1)), TrailingMinusNumbers:=True

Is this a problem with the .Select?

Thanks in advance

 

[ScottR]

No that error means that no data was selected. Are you sure the sheet with the data on is active at that stage of the subroutine?

If not you will have to change your code to activate the correct sheet. Or just skip the selection so it reads

Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(19, 1)), TrailingMinusNumbers:=True

 

[NEAnthony]

Hi

I stil have the same problem.

I have the following rutine

Private Sub CommandButton2_Click()
Sheets("Data paneler").Columns("G:G").Copy
Sheets("Sheet4").Range("E1").PasteSpecial xlValues
Sheets("Data paneler").Range("B:D,I:I").Copy
Sheets("Sheet4").Range("A1").PasteSpecial xlValues
Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(19, 1)), TrailingMinusNumbers:=True
Range("F1").Select
End Sub

After Sheets("Sheet4").Range("A1").PasteSpecial xlValues excel do run a cell update that takes around 15 seconds before it continues with with the rest of the program which it failes on. Are the 15 seconds the problem?

 

[ScottR]

Private Sub CommandButton2_Click()
Sheets("Data paneler").Columns("G:G").Copy
Sheets("Sheet4").Range("E1").PasteSpecial xlValues
Sheets("Data paneler").Range("B:D,I:I").Copy
Sheets("Sheet4").Range("A1").PasteSpecial xlValues
[B]Sheets("Sheet4").[/B]Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(19, 1)), TrailingMinusNumbers:=True
[B]Sheets("Sheet4").[/B]Range("F1").Select
End Sub

That should sort it.

 

[NEAnthony]

It sort of works. If I leave out Sheets("Sheet4").Range("F1").Select it works perfect.

I guess I have a problem with the .Select command. How come?

 

[ScottR]

Private Sub CommandButton2_Click()
Sheets("Data paneler").Columns("G:G").Copy
Sheets("Sheet4").Range("E1").PasteSpecial xlValues
Sheets("Data paneler").Range("B:D,I:I").Copy
Sheets("Sheet4").Range("A1").PasteSpecial xlValues
Sheets("Sheet4").Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(19, 1)), TrailingMinusNumbers:=True
Sheets("Sheet4").Select
Sheets("Sheet4").Range("F1").Select
End Sub

I forgot you have to select the sheet first. Silly me.

 

[NEAnthony]

Perfect! Thanks a lot ScottR!