Forum Discussion

D_Marshall514's avatar
D_Marshall514
Copper Contributor
May 09, 2024
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 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!

 

excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    May 09, 2024

    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

     

5 Replies

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 09, 2024

    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

     

    • D_Marshall514's avatar
      D_Marshall514
      Copper Contributor
      May 09, 2024

      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!

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 09, 2024

        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.

Resources