7

I am trying to split a string and create a loop for going through the cells in the column.There are a few challenges:

  1. Split works for ActiveCell only.

  2. Loop goes through all cells until LastRow but populates all cells with split string values from ActiveCell only.

  3. Split of Array starts with i = 0 even though there is Option Base 1 at the beginning of the Module.

  4. How can I change the location of destination (e.g. instead of splitting string next to existing data, is there an option to manage column numbers)?

Thank you

Option Explicit
Option Base 1

Sub SplitStringLoop()

    Dim txt As String
    Dim i As Integer
    Dim y As Integer
    Dim FullName As Variant
    Dim LastRow As Single

    ReDim FullName(3)

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    txt = ActiveCell.Value

    FullName = Split(txt, "-")

    For y = 2 To LastRow

            For i = 1 To UBound(FullName)

                Cells(y, i + 1).Value = FullName(i)

            Next i

   Next y

End Sub
Improve this question

3 Answers 3

Reset to default
8

Chris Nelisen outlined the reasons, I had this code written before he posted, so I'll post it anyway.

Option Explicit

Sub SplitStringLoop()

Dim txt As String
Dim i As Integer
Dim y As Integer
Dim FullName As Variant
Dim LastRow As Single

ReDim FullName(3)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For y = 2 To LastRow
        Cells(y, 1).Select
        txt = ActiveCell.Value
        FullName = Split(txt, "-")
        For i = 0 To UBound(FullName)
           Cells(y, i + 2).Value = FullName(i)
        Next i
Next
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi Chris, thank you for the adjusted code and detailed explanations!!! The case is closed.Have a great day!!!
0

To address the issues you list

  1. Split acts on the string you pass to it. You are passing the active cell value to it.
  2. You don't update the result of split (FullName) inside the loop. So what else do you expect?
  3. Split Returns a zero-based, one-dimensional array. It says so right there in the help. Option Base 1 specifies the default lower bound, for when you don't specify it in a Dim statement.
  4. You are specifying the column in your code Cells(y, i + 1) (i + 1 in this case). If you want it somewhere else, specify a different column.
Improve this answer

1 Comment

Thank you, Chris, for your explicit comments. I am new to VBA and I really appreciate not just a code but a detailed explanation.Anyway I am still struggling with making proper changes. 1. When I replace ActiveCell with Range ( txt = Range(Cells(2, 1), Cells(LastRow, 1)).Value) it gives me a type mismatch error. Probably it is because Dim txt As String. If I change Dim txt As Range, I have an error "Object variable or With block variable not set. I think String is a correct datatype as I have to split text rather than range.Please help me.
0

this is my sulotion

Public Function CheckEmailsValid(EmailAddresses As String) As Boolean
On Error GoTo Err_1
    Dim V_Tempi As Integer
    Dim V_Email As Variant
    
    For Each V_Email In Split(EmailAddresses, ";")
        V_Tempi = V_Tempi + 1
        If CheckEmailValid(V_Email) = False Then
            MyMsgBox 2, "Email " & V_Tempi & " Is invalid"
            CheckEmailValidFew = False
            Exit Function
        End If
    Next
    CheckEmailValidFew = True

Exit_1:
    Exit Function
Err_1:
    MyMsgBox 2, "Error !!" & vbCr & Err.Number & vbCr & Err.Description
End Function
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.