This one will stump ya *Thanks Ivan
Posted by Duane Kennerson on July 08, 2001 2:40 PM
I don't even know if I can explain this.....
I need a macro that can compare the value of one cell
to a list contained in a named two column range.
When it finds the match in the named range, it then needs to
change the value of the cell that is contained in the second column
of the named range and change it to a value that is contained
in another cell on the worksheet.
ex. Value of cell one = John Smith
Named range contains a list of names including
John Smith. The second column of the named range contains
John Smiths current phone number.
Now I need to change John Smiths old phone number
with a new number that is contain in another cell on the
worksheet.
Make sense (yeah right)
Any help would be appreciated.
Thanks.
And thanks Ivan for the currency formating problem I
was having in case you read this post.
Posted by Ivan F Moala on July 08, 2001 3:39 PM
Duane
This may help.....unless I read/understood wrongly
Option Explicit
Sub Find_Change()
Dim Adr As String
Dim Person As Range
Dim NewNumber
Dim PerName As String
PerName = [A1] 'The persons name to search for
NewNumber = [A2]'The new number
'NB: [A1] notation = range("A1") etc change to suit
On Error Resume Next
Set Person = Range("NamedRg").Find(What:=PerName, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Person Is Nothing Then GoTo NoName
On Error GoTo 0
Adr = Person.Address
Range(Adr).Offset(0, 1) = NewNumber
MsgBox PerName & "'s number has been successfully changed to:= " & NewNumber
Exit Sub
NoName:
MsgBox "Sorry " & PerName & " doesn't exist in your Defined Name Range"
End Sub
Posted by Duane Kennerson on July 09, 2001 1:45 PM
You read me perfectly. I'm amazed. The only change is that
instead of LookIn:=xlFormulas, I had to change it to xlValues.
I kept getting the "Sorry Message" when I knew the name was
in the range. But after that small change, it works perfectly.
Thanks again.
Duane
