Replacing a cell value with the value from an adjacent cell.

[Fleone]

I would like to search Column A for a value ($noname), when that value is
found, copy the data adjacent to it in Column B into Column A.

Example:
A B
Bob blank
Joe blank
Ed blank
$noname Fred

After executing the code, the column data would be:
A B
Bob blank
Joe blank
Ed blank
Fred Fred

The number of entries in Column A could vary from instance to instance (each
week).
This would need to be added to pre-existing code that is executed as a macro.

Thank you in advance for any assistance.
Fleone

 

[Gary''s Student]

Sub NameFixer()
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, 1).Value = "$noname" Then
Cells(i, 1).Value = Cells(i, 2).Value
End If
Next
End Sub

 

[Mike H]

Hi,

Try this

With Sheets("Sheet1")
lastrow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = .Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = "$noname" Then
c.Value = c.Offset(, 1).Value
End If
Next
End With

Mike

 

[Rick Rothstein]

Or, more than likely, even this reasonably efficient code...

Dim R As Range
On Error Resume Next
For Each R In Columns("B").SpecialCells(xlCellTypeConstants)
If R.Offset(0, -1).Value = "$Noname" Then R.Offset(0, -1).Value = R.Value
Next