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

[vba]

Set CopyNames = Selection
[/vba]

or

[vba]

Set CopyNames = Range("A1:H10")
[/vba]

[inked]

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

-ep

[Bob Phillips]

[vba]

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
[/vba]
or even directly

[vba]

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
[/vba]

[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.