Variable Not Defined

[inked]

I'm extremely new to VBA. I've had some help with the following macro:



Option Explicit
Dim Cell As Range
Dim CopyNames As Range

Sub ShuffleNames()
Dim FirstName As String
Dim LastName As String
Dim CommaLoc As Long
For Each Cell In CopyNames
If Not IsEmpty(Cell.Value) Then
CommaLoc = InStr(Cell.Value, ",")
LastName = Left(Cell.Value, CommaLoc - 1)
FirstName = Right(Cell.Value, Len(Cell.Value) - CommaLoc)
LastName = Application.Proper(LastName)
FirstName = Application.Proper(FirstName)
Cell.Value = FirstName & " " & LastName
End If
Next Cell
End Sub


I've placed it in a module and defined the range within the workbook. I keep getting and error saying object required and highlighting "For Each Cell In CopyNames"

I'm using Office 2004 for the Mac.

Thanks.

-ep

[Bob Phillips]

That is because, as defined, CopyNames is not initialised, so there is nothing to iterate through. You need to initilaise it, perhaps like



Set CopyNames = Selection


or



Set CopyNames = Range("A1:H10")

[inked]

Where do I initialize it? My Range is 'Sheet 1'!A1:A4.

-ep

[Bob Phillips]

Option Explicit
Dim Cell As Range
Dim CopyNames As Range

Sub ShuffleNames()
Dim FirstName As String
Dim LastName As String
Dim CommaLoc As Long
Dim CopyNames As Range

Set CopyNames = Worksheets("Sheet1").Range("A1:A4")
For Each Cell In CopyNames
If Not IsEmpty(Cell.Value) Then
CommaLoc = InStr(Cell.Value, ",")
LastName = Left(Cell.Value, CommaLoc - 1)
FirstName = Right(Cell.Value, Len(Cell.Value) - CommaLoc)
LastName = Application.Proper(LastName)
FirstName = Application.Proper(FirstName)
Cell.Value = FirstName & " " & LastName
End If
Next Cell
End Sub

or even directly



Option Explicit
Dim Cell As Range

Sub ShuffleNames()
Dim FirstName As String
Dim LastName As String
Dim CommaLoc As Long
Dim CopyNames As Range

For Each Cell In Worksheets("Sheet1").Range("A1:A4")
If Not IsEmpty(Cell.Value) Then
CommaLoc = InStr(Cell.Value, ",")
LastName = Left(Cell.Value, CommaLoc - 1)
FirstName = Right(Cell.Value, Len(Cell.Value) - CommaLoc)
LastName = Application.Proper(LastName)
FirstName = Application.Proper(FirstName)
Cell.Value = FirstName & " " & LastName
End If
Next Cell
End Sub

[Zack Barresse]

What exactly is the scope of this? What do you need to do with the data? You have a high potential for overwriting values here and it doesn't make much sense.