macro question

UPN

Board Regular
Joined
May 14, 2006
Messages
138
Option Explicit

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

Unload Me

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
FirstAddress = c.Address
Do
MsgBox "Employee number already exists" & vbCrLf & _
"Please try again", vbCritical + vbOKOnly, "User Exists"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
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

' Add Exempt Status
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)
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

implemented in your code
Code:
.Offset(1, 1) = Mid(TextBox2 & " " & TextBox2, InStr(1, TextBox2, " ") + 1, Len(TextBox2))

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,119,024
Messages
5,575,646
Members
412,682
Latest member
salmanahmad84
Top