| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Force entry into cell, based on validation selection in adjacent cell | Excel Worksheet Functions | |||
| How to populate a cell with numeric value based on textselected from pull down in adjacent cell? | Excel Worksheet Functions | |||
| Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
| change current cell colour based on the value of adjacent cell on other worksheet | Excel Programming | |||
| Auto-fill cell based on adjacent cell information.. | Excel Worksheet Functions | |||
Linear Mode
