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

Hi Rachel,

Try prefixing the search string with an additional tilde (~) character,
e.g.:

res = Cells.Find(What:="~~ABC", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

---
Regards,
Norman



"rachel" wrote in message
...
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

Norman,

The problem is, i'm actually searching on a variable (prodName) that gets
its value from a cell in Sheet1 whose data is automatically pulled from an
external source. I'm trying to match on the entire product name because some
products have very similar names. I can't search on just a piece of the
product name. I'm not sure how to automatically insert a second ~ into the
name, and don't think that would be a good solution anyway since the data are
coming from an external source and the data on this sheet need to match other
data people are using, including data on other sheets in the workbook.

Is there another way around this?

thanks
rachael

"Norman Jones" wrote:

Hi Rachel,

Try prefixing the search string with an additional tilde (~) character,
e.g.:

res = Cells.Find(What:="~~ABC", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

---
Regards,
Norman



"rachel" wrote in message
...
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

Hi Rachel,

Using the search string What:="~~" and using LookAt:=xlPart (rather than
LookAt:=xlWhole), should find any string which includes a tilde character.


---
Regards,
Norman



"rachael" wrote in message
...
Norman,

The problem is, i'm actually searching on a variable (prodName) that gets
its value from a cell in Sheet1 whose data is automatically pulled from an
external source. I'm trying to match on the entire product name because
some
products have very similar names. I can't search on just a piece of the
product name. I'm not sure how to automatically insert a second ~ into the
name, and don't think that would be a good solution anyway since the data
are
coming from an external source and the data on this sheet need to match
other
data people are using, including data on other sheets in the workbook.

Is there another way around this?

thanks
rachael

"Norman Jones" wrote:

Hi Rachel,

Try prefixing the search string with an additional tilde (~) character,
e.g.:

res = Cells.Find(What:="~~ABC", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

---
Regards,
Norman



"rachel" wrote in message
...
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

Hi Norman,

Ok, I finally get it! Instead of having prodName (what:=prodName) just be
the product name in the row where a person is making the change [prodName =
Sheets("data").Cells(ChangedRow,1).Value], i changed prodName to be
"Replace(Sheets("data").Cells(ChangedRow, 1).Value, "~", "~~")".

Now, when i run Find to match the product names on the two sheets, it works.

Thank you so much! And thanks for responding so quickly!
rachael

"Norman Jones" wrote:

Hi Rachel,

Using the search string What:="~~" and using LookAt:=xlPart (rather than
LookAt:=xlWhole), should find any string which includes a tilde character.


---
Regards,
Norman



"rachael" wrote in message
...
Norman,

The problem is, i'm actually searching on a variable (prodName) that gets
its value from a cell in Sheet1 whose data is automatically pulled from an
external source. I'm trying to match on the entire product name because
some
products have very similar names. I can't search on just a piece of the
product name. I'm not sure how to automatically insert a second ~ into the
name, and don't think that would be a good solution anyway since the data
are
coming from an external source and the data on this sheet need to match
other
data people are using, including data on other sheets in the workbook.

Is there another way around this?

thanks
rachael

"Norman Jones" wrote:

Hi Rachel,

Try prefixing the search string with an additional tilde (~) character,
e.g.:

res = Cells.Find(What:="~~ABC", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

---
Regards,
Norman



"rachel" wrote in message
...
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