Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

split then add to array working before then suddenly not working

i have a code below it was working fine then suddenly the line arr = split(cell.Value, ",") has an error wrong number of arguments or invalid property assignment i just inserted the lastRow variable, even if i changed the range something fixed like A1:A10 it is still the same error

Sub split()
        Dim rng As Range
        Dim cell As Range
        Dim arr() As String
        Dim i As Long, lastRow As Long
        
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range("A1:A" & lastRow) ' Range of cells to check
        
        For Each cell In rng
        If cell.Value <> "" Then
            If InStr(1, cell.Value, ",") = 0 Then
                Cells(cell.Row, 3) = cell.Value
                Cells(cell.Row, 1) = ""
            Else
                arr = split(cell.Value, ",") ' Split cell value by comma and add to array
            
                For i = UBound(arr) To 0 Step -1
                     If UBound(arr) = 2 Then
                     Cells(cell.Row, i + 1) = arr(i)
                     Else
                     Cells(cell.Row, i + 2) = arr(i)
                     Cells(cell.Row, 1) = ""
                     End If
                Next i
               End If
               End If
        Next cell
        
    End Sub

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

This is the danger of using names for subs/variables that already exist in the VBA code/naming syntax. Your error is caused by it thinking you’re trying to call your own sub recursively.

Notice how it’s a small s unlike Split() but your sub doesn’t take any arguments hence that error. Change the name of your sub and adjust your line to arr = Split(cell.Value, ",") and it should be fixed.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading