I'm working with 2 worksheets. Sheet1 captures info about various products.
Most of the info is brought in automatically from an external data source.
People occassionally make manual changes to the info on Sheet1. When the data
are refreshed, they want those changes to be kept. I'm doing this now by
saving the changes they make to sheet Sheet2 and updating Sheet1 with the
info stored on Sheet2.

If they make a second change to a product, i want that information updated
on that product's line on Sheet2, rather than having a new line of
information saved. Right now, I'm doing this by using the Find method. When a
person makes a change to a product, my code looks for that product name on
Sheet2 - if the product already exists there, the info is updated on Sheet2.
If the product doesn't already exist, a new line is added with the updated
info.

In general, the code works fine. But, i'm running into difficulty when the
product name contains "~" (e.g., "Product ~ ABC"). The Find method doesn't
recognize a match if there is product name containing "~" that exists on both
Sheet1 and Sheet2. Instead, it always creates a new line on Sheet2 for
products like "Product ~ ABC".

Why won't it find a match when there's a ~? Is there any way I can work
around this? Below is what I have so far.

thanks!
rachael


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("D:D")) Is Nothing Then
Dim StoredRow As Integer, ChangedRow As Integer, prodName As String, c
As Range, chgRow As Integer

ChangedRow = Target.Row
prodName = Sheets("Data").Cells(ChangedRow, 1).Value
StoredRow = Sheets("Stored Changes").Cells(1,
1).CurrentRegion.Rows.Count + 1

With Sheets("Stored Changes").Range("A:A")
Set c = .Find((prodName), LookIn:=xlValues)
If Not c Is Nothing Then
chgRow = c.Row
.Cells(chgRow, 2).Value = Sheets("Data").Cells(ChangedRow, 4)
Else
.Cells(StoredRow, 1).Value = Sheets("Data").Cells(ChangedRow, 1)
.Cells(StoredRow, 2).Value = Sheets("Data").Cells(ChangedRow, 4)
End If
End With
Else
Exit Sub
End If

End Sub