Hi I have a following problem

Let us assume what i have Excel sheet with two columns A and B

Current situation Wanted result
A B A B
Hs Hs Hs Hs
Kht Undef Kht Kht
HsMr Hs HsMr HsMr

I want to make a macro which copy/paste or replaces values in column B
based on cell location in column A, So if the cell value in column B
is Undef the value Undef in column B should be always replaced by
value of cell beside from column A. E.g If macro founds the value
"undef" or Value "*Mr" in column B it replaces these values in column
B on basis of values found in column A beside that particular cell.

Any ideas or related code to solve the problem?

The description for Mr doesn't match the example. I worked according to the
description

Sub UpdateValues()
Dim i As Long

Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If LCase(Cells(i, "B").Value) = "undef" Then
Cells(i, "B").Value = Cells(i, "A").Value
ElseIf LCase(Cells(i, "B").Value) Like "mr" Then
Cells(i, "B").Value = Cells(i, "A").Value
End If
Next i
Application.ScreenUpdating = True

End Sub

Save your data before running.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"spolk" wrote in message
om...
Hi I have a following problem

Let us assume what i have Excel sheet with two columns A and B

Current situation Wanted result
A B A B
Hs Hs Hs Hs
Kht Undef Kht Kht
HsMr Hs HsMr HsMr

I want to make a macro which copy/paste or replaces values in column B
based on cell location in column A, So if the cell value in column B
is Undef the value Undef in column B should be always replaced by
value of cell beside from column A. E.g If macro founds the value
"undef" or Value "*Mr" in column B it replaces these values in column
B on basis of values found in column A beside that particular cell.

Any ideas or related code to solve the problem?