VBA First Name

[BrianM]

Hello all,
Using VBA, what is a simple line of code to obtain the first name from A1 (John Doe) so that I can set it as a variable?

My brain says
Dim FName as string
FName =LEFT(A2,SEARCH(“ ”,A2)-1) but I know that won't work. I don't want to use a formula in a helper cell to only then set the variable.

Thanks

 

[Eric W]

Try

FName = Left(Range("A1"), InStr(Range("A1") & " ", " ") - 1)

or

FName = Split(Range("A1"))(0)

 

[BrianM]

Try

FName = Left(Range("A1"), InStr(Range("A1") & " ", " ") - 1)

or

FName = Split(Range("A1"))(0)

Perfect

Thanks

 

[Eric W]

You're welcome.

 

[Rick Rothstein]

Perfect

There is no such thing as "perfect" when it comes to names. Eric's code (pretty much any code) will fail to handle multiple first names. For example, back in my working days, I had a co-worker whose given (legal) first name was Mary Ann (with the space between them).

 

[BrianM]

There is no such thing as "perfect" when it comes to names. Eric's code (pretty much any code) will fail to handle multiple first names. For example, back in my working days, I had a co-worker whose given (legal) first name was Mary Ann (with the space between them).

Agreed... With that being said, this is what I'm using
FNameGR = Split(Range("B27"))(0)
FNameMAR = Split(Range("B28"))(0)
FNameFTR = Split(Range("B29"))(0)

However, there are cases where any one of these cells could be empty, how would I handle those cases? As is, I get Subscript out of range when I run into an empty cell.

Thanks

 

[Eric W]

What do you want in case of an empty cell? The first formula I posted won't give you the Subscript out of range error, it will just return a null string. If you want something else, then let us know and we can figure something out.

 

[BrianM]

What do you want in case of an empty cell? The first formula I posted won't give you the Subscript out of range error, it will just return a null string. If you want something else, then let us know and we can figure something out.

Something like the following:

If IsEmpty(Range("B27").Value) = True Then
MsgBox "Government Rep field is empty."
Else

FNameGR = Split(Range("B27"))(0)

End If

If IsEmpty(Range("B28").Value) = True Then
MsgBox "Mission Assurance Representative field is empty."
Else

FNameMAR = Split(Range("B28"))(0)

End If

If IsEmpty(Range("B29").Value) = True Then
MsgBox "Functional / Technical Representative."

Else

FNameFTR = Split(Range("B29"))(0)

End If
Something like that...

 

[Eric W]

That's seems fine, although you might want to try:

If Range("B27").Value = "" Then
    MsgBox [COLOR=#333333]"Government Rep field is empty."
    Exit Sub
Else
    [/COLOR][COLOR=#333333]FNameGR = Split(Range("B27"))(0)[/COLOR]
[COLOR=#333333]End If

etc . . . [/COLOR]

 

[BrianM]

That's seems fine, although you might want to try:

If Range("B27").Value = "" Then
    MsgBox [COLOR=#333333]"Government Rep field is empty."
    Exit Sub
Else
    [/COLOR][COLOR=#333333]FNameGR = Split(Range("B27"))(0)[/COLOR]
[COLOR=#333333]End If

etc . . . [/COLOR]

Perfect :D

Thanks