Forum Discussion
Solved
Run-Time Error '1004': Method 'Range' of object '_global' failed?
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 perform...
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
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!