Forum Discussion
Run-Time Error '1004': Method 'Range' of object '_global' failed?
Sub texttocolumns() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim currentcell As Variant Set currentcell = Cells(8, 10) Do While Not IsEmpty(currentcell) currentcell.texttocolumns Destination:=currentcell, _ ConsecutiveDelimiter:=True, Space:=True Set currentcell = currentcell.Offset(2, 0) Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End SubThis macro works in the attached sample file. The dimension of currentcell must be Variant according to the Microsoft documentation.
Range.TextToColumns-Methode (Excel) | Microsoft Learn
Sub texttocolumns()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim currentcell As Variant
Set currentcell = Cells(8, 10)
Do While Not IsEmpty(currentcell)
currentcell.texttocolumns Destination:=currentcell, _
ConsecutiveDelimiter:=True, Space:=True
Set currentcell = currentcell.Offset(2, 0)
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End SubThis macro works in the attached sample file. The dimension of currentcell must be Variant according to the Microsoft documentation.
Range.TextToColumns-Methode (Excel) | Microsoft Learn
OliverScheurich I've got one more for you if you've got time. This one is giving me an Invalid Procedure Call or Argument error. It seems like it thinks var is going negative (it's defined as a string and has more than 30 characters in it). I'm assuming I'm referencing something incorrectly... Please help!
Your code works as intended when i run it two times in my sample file. After running it 2 times only "abcd" remains in cell B8. If i then run the code a third time the Runtime Error 5 is thrown because the length of abcd is 4. Then this statement would be negative which returns the error:
Len(var) - 15
If you hover of this
Len(var)
with the mouse can you check which result is returned in your file?
Here is what`s returned in my sheet when i run the code a 3rd time. Len(var) = 4 and this minus 15 would result in a negative number.
- Thanks for taking a look. It looks like I had another Macro that was somehow screwing it up and causing the error. I've got it resolved now. Thank you so much for your help!