# macro question

#### UPN

##### Board Regular
Option Explicit

Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim ws As Worksheet
Dim c As Range
Set ws = Sheets("Employees")
Set LastRow = ws.Range("AE65536").End(xlUp)

Application.ScreenUpdating = False
ws.Activate
With ws.Range([AE8], [AE65536].End(xlUp))
Set c = .Find(TextBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
Do
MsgBox "Employee number already exists" & vbCrLf & _
"Please try again", vbCritical + vbOKOnly, "User Exists"
Set c = .FindNext(c)
UserForm2.Show
End If
End With
Sheets("User Form").Activate
Application.ScreenUpdating = True

With LastRow
' Employee #
.Offset(1, 0) = TextBox1.Value
' Employee Name
.Offset(1, 1) = TextBox2.Value
' Start Date
.Offset(1, 2) = TextBox3.Value
End With

If OptionButton1 = True Then LastRow.Offset(1, 3) = "Exempt"
If OptionButton2 = True Then LastRow.Offset(1, 3) = "NonExempt"
If OptionButton3 = True Then LastRow.Offset(1, 3) = "PartTime"

End Sub

i have this macro that makes a table of employees. to put the person name like this Joe SMoe

can i use the formula listed below in macro to change it to Smoe, Joe

=RIGHT(AF9,LEN(AF9)-FIND(" ",AF9))&", "&LEFT(AF9,FIND(" ",AF9)-1)

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### erik.van.geit

##### MrExcel MVP
Hi,

Barry Houdini learned me a shorter formula yesterday:
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

Code:
``.Offset(1, 1) = Mid(TextBox2 & " " & TextBox2, InStr(1, TextBox2, " ") + 1, Len(TextBox2))``

kind regards,
Erik

#### bam12

##### Well-known Member

but could you tell me where to add it

#### bam12

##### Well-known Member
figured it out

i figured out where it went

is UPN = bam12

Replies
4
Views
55
Replies
1
Views
49
Replies
0
Views
184
Replies
0
Views
36
Replies
6
Views
124