The Split() function

[Gilbfernandes]

Good morning everyone,

I have the following code but I am having problem with it:

New_Name = Split(Sheets("Report").Range("D16"))

FirstName = New_Name(0)
LastName = New_Name(1)

If LastName = "" Then
MsgBox "Please enter Last Name"
GoTo endsub
End If

But if Last Name is not entered on D16 , I get Run-Time Error 9 on LastName=New_Name(1). I don't even know if the IF will work on this case.

Can someone help me?

Thank you in advance.

Gil

 

[Fluff]

How about

Sub Gilbfernandes()
   Dim NewName As Variant
   NewName = Split(Sheets("Report").Range("D16"))
   If UBound(NewName) = 0 Then
      MsgBox "Please enter Last Name"
      Exit Sub
   End If
End Sub

 

[JLGWhiz]

First of all, the statement as you have typed it into the thread would likely error for lack of arguments.
Maybe something like this would work

New_Name = Split(Sheets("Report").Range("D16").Value, ",")
If UBound(New_Name) = 0 Then
    FistName = New_Name(LBound(New_Name))
    MsgBox "Please Enter Last Name", vbExclamation, "MISSING NAME"
    Exit Sub
Else
    FirstName = New_Name(LBound(New_Name))
    LastName = New_Name(UBound(New_Name))
End If

 

[Yongle]

Or relying on trapping the error ...

    Dim FirstName As String, LastName As String, New_Name As Variant
    On Error Resume Next
    New_Name = Split(Sheets("Report").Range("D16"))
    FirstName = New_Name(0)
    LastName = New_Name(1)
    On Error GoTo 0
    If LastName = "" Then MsgBox "Enter last name", , ""
    '..etc

 

[G12]

Hi.
Just thinking through it, does any of the good option above trap the error if someone enters "First " in which Split recognizes that the trailing space is an entry?

Ah. Didn't see your post Yongle. Nice

 

[Yongle]

trap the error if someone enters "First " in which Split recognizes that the trailing space is an entry?

To get rid of any extra spaces anywhere in the string ...

New_Name = Split(WorksheetFunction.Trim(Sheets("Report").Range("D16")))

 

[Gilbfernandes]

How about

Sub Gilbfernandes()
   Dim NewName As Variant
   NewName = Split(Sheets("Report").Range("D16"))
   If UBound(NewName) = 0 Then
      MsgBox "Please enter Last Name"
      Exit Sub
   End If
End Sub

Thank you Fluff, It worked. I really appreciate your help.

 

[Gilbfernandes]

Thank you every one for all the good and helpful ideas.

 

[Fluff]

Glad we could help & thanks for the feedback