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

 

[Simon Lloyd]

or even
Code
-------------------
Dim Rng As Range, MyCell As Rang
Set Rng = ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row
For Each MyCell In Rn
If LCase(MyCell.Value) = LCase("$noname") The
MyCell.Value = MyCell.Offset(0, 1).Valu
End I
Next MyCel
-------------------

Hi

Try thi

With Sheets("Sheet1"
lastrow = .Cells(Cells.Rows.Count, "A").End(xlUp).Ro
Set MyRange = .Range("A1:A" & lastrow
For Each c In MyRang
If c.Value = "$noname" The
c.Value = c.Offset(, 1).Valu
End I
Nex
End Wit

Mik





a macro

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[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