I am having trouble getting this to work. I have a list of customers some
with specific notes assigned to them and some without. What I want to do is
be able to run this macro that compares the two lists and inserts the notes
as required, but I do not want the existing notes in the master list to be
affected or changed.

This is my macro that copies over the notes from one sheet to the master.

Sub Add_Notes_To_Master()

Range("C5:C500").Select
ActiveWorkbook.Names.Add Name:="Sorted", RefersToR1C1:= _
"='Sales By State'!R5C3:R500C3"
Sheets("CrosstabSales2_US$_RegionSalesR").Select
Range("F2:F696").Select
ActiveWorkbook.Names.Add Name:="Active_Accounts", RefersToR1C1:= _
"='CrosstabSales2_US$_RegionSalesR'!R2C6:R696C 6"

Set MyFirstRange = Range("Active_Accounts")
Set MySecondRange = Range("Sorted")
fnd = 0

For Each C In MyFirstRange
For Each n In MySecondRange
If C.Value = n.Value Then
C.Offset(0, 19).Value = n.Offset(0, 4).Value
fnd = 1
End If
Next
Next
Range("B2").Select
End Sub

I can't seam to figure out the VBA to tell the above to ignore the customers
cells with notes already inputted in them.
--
Pete

Add another IF statement to check if the destination cell in MySecondRange is
blank or not before preceding. For example,
For Each n In MySecondRange
If C.Value = n.Value Then
C.Offset(0, 19).Value = n.Offset(0, 4).Value
fnd = 1
End If
Next


----------
Thanks,
Anony


"Pete" wrote:

I am having trouble getting this to work. I have a list of customers some
with specific notes assigned to them and some without. What I want to do is
be able to run this macro that compares the two lists and inserts the notes
as required, but I do not want the existing notes in the master list to be
affected or changed.

This is my macro that copies over the notes from one sheet to the master.

Sub Add_Notes_To_Master()

Range("C5:C500").Select
ActiveWorkbook.Names.Add Name:="Sorted", RefersToR1C1:= _
"='Sales By State'!R5C3:R500C3"
Sheets("CrosstabSales2_US$_RegionSalesR").Select
Range("F2:F696").Select
ActiveWorkbook.Names.Add Name:="Active_Accounts", RefersToR1C1:= _
"='CrosstabSales2_US$_RegionSalesR'!R2C6:R696C 6"

Set MyFirstRange = Range("Active_Accounts")
Set MySecondRange = Range("Sorted")
fnd = 0

For Each C In MyFirstRange
For Each n In MySecondRange
If C.Value = n.Value Then
C.Offset(0, 19).Value = n.Offset(0, 4).Value
fnd = 1
End If
Next
Next
Range("B2").Select
End Sub

I can't seam to figure out the VBA to tell the above to ignore the customers
cells with notes already inputted in them.
--
Pete

Seemed to pressed enter by accident last time. This is the correct post.

Add another IF statement to check if the destination cell in MySecondRange
is blank or not before proceeding. For example,
For Each n In MySecondRange
If C.Value = n.Value Then
If n.Offset(0,4).Value = "" Then
C.Offset(0, 19).Value = n.Offset(0, 4).Value
fnd = 1
End If
End If
Next

----------
Cheers,
Anony


"Pete" wrote:

I am having trouble getting this to work. I have a list of customers some
with specific notes assigned to them and some without. What I want to do is
be able to run this macro that compares the two lists and inserts the notes
as required, but I do not want the existing notes in the master list to be
affected or changed.

This is my macro that copies over the notes from one sheet to the master.

Sub Add_Notes_To_Master()

Range("C5:C500").Select
ActiveWorkbook.Names.Add Name:="Sorted", RefersToR1C1:= _
"='Sales By State'!R5C3:R500C3"
Sheets("CrosstabSales2_US$_RegionSalesR").Select
Range("F2:F696").Select
ActiveWorkbook.Names.Add Name:="Active_Accounts", RefersToR1C1:= _
"='CrosstabSales2_US$_RegionSalesR'!R2C6:R696C 6"

Set MyFirstRange = Range("Active_Accounts")
Set MySecondRange = Range("Sorted")
fnd = 0

For Each C In MyFirstRange
For Each n In MySecondRange
If C.Value = n.Value Then
C.Offset(0, 19).Value = n.Offset(0, 4).Value
fnd = 1
End If
Next
Next
Range("B2").Select
End Sub

I can't seam to figure out the VBA to tell the above to ignore the customers
cells with notes already inputted in them.
--
Pete