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

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

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

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

--
Gary''s Student - gsnu200907


"Fleone" wrote:

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

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

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

"Fleone" wrote:

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

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

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


Mike H;526506 Wrote:
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

"Fleone" wrote:

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



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144589

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

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

--
Rick (MVP - Excel)


"Simon Lloyd" wrote in message
...

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


Mike H;526506 Wrote:
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

"Fleone" wrote:

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



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=144589