Macro code to replace data

[constantinet]

If Column C has a certain name I would like that name to be copied over to Column A. I am unable to just do a copy and pasta as I do not need every name copied over to Column A. So for example if Column A has the following number 12345 and Column C has the name Jane Doe than I would like to replace the number in Column A with the name from Column C.

 

[DanteAmor]

Let's start with the following:

Sub replace_data()
    Set f = Range("C:C").Find("Jane Doe", LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        Range("A" & f.Row).Value = f.Value
    End If
End Sub

 

[constantinet]

Thank you for your help. I forget one important piece of information sometimes the name Jane Doe repeats itself in Column C and when this happens I need the name moved multiple times to Column A. The above macro currently recognizes the name once, is there away to recognize the name multiple times.

Thank you again and sorry for leaving out that piece of information.

 

[DanteAmor]

Thank you for your help. I forget one important piece of information sometimes the name Jane Doe repeats itself in Column C and when this happens I need the name moved multiple times to Column A. The above macro currently recognizes the name once, is there away to recognize the name multiple times.

Thank you again and sorry for leaving out that piece of information.

Ok, then let's get on with this.

Sub replace_data()
    Dim r As Range, f As Range, cell As String
    Set r = Range("C:C")
    Set f = r.Find("Jane Doe", LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        cell = f.Address
        Do
            Range("A" & f.Row).Value = f.Value
            Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
    End If
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub JaneDoe()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(C1:C#=""Jane Doe"",C1:C#,IF(A1:A#="""","""",A1:A#))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]