Find & Replace within a specific column

[nyrangers1994]

I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.

The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.

For example, if I have:

Row 3 Name
Row 4
Row 5 John Smith
Row 6 Jane Doe
Row 7 John Doe
Row 8 Jane Smith

I'd like the data to become:

Row 3 Name
Row 4
Row 5 John^Smith
Row 6 Jane^Doe
Row 7 John^Doe
Row 8 Jane^Smith

Thanks for any help you can provide. I'm using Excel 2010 if that impacts anything.

 

[tlowry]

Sub main()
    Set fnd = Rows(3).Find("Name")
    If fnd Is Nothing Then Exit Sub
    lmaxrow = Cells(Rows.Count, fnd.Column).End(xlUp).Row
    If lmaxrow < 3 Then Exit Sub
    For i = 3 To lmaxrow
        Cells(i, fnd.Column) = Replace(Cells(i, fnd.Column), " ", "^")
    Next i
End Sub