Marco that can replaces the cell value

Closed
Report
Posts
3
Registration date
Monday April 11, 2011
Status
Member
Last seen
April 11, 2011
-
 lefox -
Hello Excel Marco Experts,

I need a marco that can replaces the cell value in column A from cell value in column B,
if it finds cell value in column A equal to exactly "CH"

Thanks.

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
Sub test()
Dim r As Range, c As Range
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
If c = "CH" Then c = c.Offset(0, 1)
Next c
End Sub
Posts
3
Registration date
Monday April 11, 2011
Status
Member
Last seen
April 11, 2011

it works~! thanks expert
Posts
3
Registration date
Monday April 11, 2011
Status
Member
Last seen
April 11, 2011

by the way, can you explain to me the purpose of each line? \P
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
it takes the range from A2 down (A1 is heading)
and loops through each cell and checks whether each cells is having an entry "CH" If it is so the macro copies B value to CORRESPONDING cell in column A
otherwise it goes to the next cell in the range without doing anything.
set r=......
this sets the range
for each c in r
looping starts
if c= "CH"....
this is clear (c is cell in A and c.offset(0,1) one column to the right that is B column
next c
this looping technique
when it reaches the last cell in the range r the looping stops

to learn vba you RECORD a macro. The codes for each step will be translated in the subroutine and you can study each code statement.
Perhaps these code statements need some tweaking.

you can also get a good book on vba for your version of excel
thank you sir!