Run-Time Error '1004': Method 'Range' of object '_global' failed?

[D_Marshall514]

I have a macro that is supposed to take a cell (starting with J8), perform the text to columns function (placing the data in 2 columns starting with J8) and then go down two rows (to J10) and performs the same function.
It is intended to continue this process until it reaches an empty cell, but I'm getting this error and can't figure out why.  Any tips or advice would be greatly appreciated.  Thanks!

[#2 · Accepted solution]

@D_Marshall514 

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 Sub

This 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

[#3]

That works! Thank you so much!

[#4]

@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!

[#5]

@D_Marshall514 

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.

[#6]

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!