fail to combine string VBA Excel after I Split that string-CodePudding

I try to skip one word from a string after that i want to combine string that already split into one, using "for" looping but, when I want to combine again the split word i can't get result that i want.

Here is My Code:

Function FindData(lookup_value As String, tbl_array As Range) As String
Dim Text As String, SplitText() As String
Dim j As Integer, CountText As Integer

SplitText = Split(lookup_value)
CountText = UBound(SplitText())   1

For j = 1 To CountText
Text = Text & SplitText(j) & " "
Next j

FindData = Text
End Function

The Input is:

=FindData(A2,C2)

Where Value A2 = "Aku Kamu Dia" C2 skip cause c2 still not use here

My Hope Result:

Kamu Dia

But I get :

#VALUE!

Someone Plis help me

CodePudding user response：

You missed delimiter argument of Split() function. From your expected result, it seems you are trying skip first word. So, try to concatenate from 2nd element of array. Start loop from 1 as array position always start from 0 zero.

Function FindData(lookup_value As String, tbl_array As Range) As String
Dim Text As String, SplitText() As String
Dim j As Integer, CountText As Integer

SplitText = Split(lookup_value, " ")

    For j = 1 To UBound(SplitText)
        Text = Text & SplitText(j) & " "
    Next j

FindData = Text
End Function