Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

..Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Worked a treat Alan!!!

Your a star!!!!

Do you happen to know about ms access?

I have reports within an access DB that I want automatically emailed
using a macro each night based on the system time. The report should
be sent to three people.

Is this possible?

Thanks

Stuart



wrote:
I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

Glad it worked for you. Happy to help. I have not done much VBA work
in Access. You could check out Ron De Bruin's site
http://www.rondebruin.nl/. I've successfully used his coding within
Excel for mailing ranges and spreadsheets. There may be some advice on
Access as well. If Outlook is not your mail app., you could post to
the Access group for some help.

Alan


Stuart wrote:
Worked a treat Alan!!!

Your a star!!!!

Do you happen to know about ms access?

I have reports within an access DB that I want automatically emailed
using a macro each night based on the system time. The report should
be sent to three people.

Is this possible?

Thanks

Stuart



wrote:
I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Hi Alan,

Sorry to bother you again,

It turns out that we will be doing some parallel running of the two
systems and wondered if you were able to amend your macro slightly.

I still want the macro to look up column A in one worksheet and column
A in another however instead of replacing the text in worksheet 2 with
the contents of worksheet 2 I would like it to add to the values in
sheet 2.

An example :

Worksheet 1

MCDEBH 20 30 40 50 60

Work sheet 2 contains

MCDEBH 10 30 40 50 60
asdsdssd 20 20 40 10 10

My expected output after the macro has been run is

MCDEBH 30 60 80 100 120
asdsdssd 20 20 40 10 10

Can you help with this Alan?

Thanks mate

Stuart



wrote:
Stuart,

Glad it worked for you. Happy to help. I have not done much VBA work
in Access. You could check out Ron De Bruin's site
http://www.rondebruin.nl/. I've successfully used his coding within
Excel for mailing ranges and spreadsheets. There may be some advice on
Access as well. If Outlook is not your mail app., you could post to
the Access group for some help.

Alan


Stuart wrote:
Worked a treat Alan!!!

Your a star!!!!

Do you happen to know about ms access?

I have reports within an access DB that I want automatically emailed
using a macro each night based on the system time. The report should
be sent to three people.

Is this possible?

Thanks

Stuart



wrote:
I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

No problem. I will take a look at it and repost

Stuart wrote:
Hi Alan,

Sorry to bother you again,

It turns out that we will be doing some parallel running of the two
systems and wondered if you were able to amend your macro slightly.

I still want the macro to look up column A in one worksheet and column
A in another however instead of replacing the text in worksheet 2 with
the contents of worksheet 2 I would like it to add to the values in
sheet 2.

An example :

Worksheet 1

MCDEBH 20 30 40 50 60

Work sheet 2 contains

MCDEBH 10 30 40 50 60
asdsdssd 20 20 40 10 10

My expected output after the macro has been run is

MCDEBH 30 60 80 100 120
asdsdssd 20 20 40 10 10

Can you help with this Alan?

Thanks mate

Stuart



wrote:
Stuart,

Glad it worked for you. Happy to help. I have not done much VBA work
in Access. You could check out Ron De Bruin's site
http://www.rondebruin.nl/. I've successfully used his coding within
Excel for mailing ranges and spreadsheets. There may be some advice on
Access as well. If Outlook is not your mail app., you could post to
the Access group for some help.

Alan


Stuart wrote:
Worked a treat Alan!!!

Your a star!!!!

Do you happen to know about ms access?

I have reports within an access DB that I want automatically emailed
using a macro each night based on the system time. The report should
be sent to three people.

Is this possible?

Thanks

Stuart



wrote:
I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart

Stuart,

Try this. It will add the value in sheet 2 to the value in sheet 1.

Alan

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Application.CutCopyMode = False
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
Sheets(2).Range(Myfind.Address).EntireRow.Copy
Sheets(1).Range(MyCell.Address).PasteSpecial xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
Application.CutCopyMode = False

' End If
MoveOn:
Next
Range("A1").Select
End Sub

Stuart wrote:
Hi Alan,

Sorry to bother you again,

It turns out that we will be doing some parallel running of the two
systems and wondered if you were able to amend your macro slightly.

I still want the macro to look up column A in one worksheet and column
A in another however instead of replacing the text in worksheet 2 with
the contents of worksheet 2 I would like it to add to the values in
sheet 2.

An example :

Worksheet 1

MCDEBH 20 30 40 50 60

Work sheet 2 contains

MCDEBH 10 30 40 50 60
asdsdssd 20 20 40 10 10

My expected output after the macro has been run is

MCDEBH 30 60 80 100 120
asdsdssd 20 20 40 10 10

Can you help with this Alan?

Thanks mate

Stuart



wrote:
Stuart,

Glad it worked for you. Happy to help. I have not done much VBA work
in Access. You could check out Ron De Bruin's site
http://www.rondebruin.nl/. I've successfully used his coding within
Excel for mailing ranges and spreadsheets. There may be some advice on
Access as well. If Outlook is not your mail app., you could post to
the Access group for some help.

Alan


Stuart wrote:
Worked a treat Alan!!!

Your a star!!!!

Do you happen to know about ms access?

I have reports within an access DB that I want automatically emailed
using a macro each night based on the system time. The report should
be sent to three people.

Is this possible?

Thanks

Stuart



wrote:
I set the code to ignore the 0 values and only look for matches against
column A of each sheet. With that, the follow is the results on sheet
one:

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

Revised code:

Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
MoveOn:
Next
End Sub
Stuart wrote:
The value in sheet 1 may not be zero however if the contents of column
A match the same as sheet 2 column A then replace the row in sheet 1
with the row in sheet 2.

Sheet 1 could perhaps contains hundreds of codes in column A sheet 1
that may not match sheet 2 column A.

Is this clearer?

If you want me to call you and explain then send me an email with your
telephone number and we can have a quick chat,

Your free drink awaits you!!

Many thanks

Stuart

wrote:
Stuart,

Just read your post again. May be my misunderstanding. But, just to
clarify, the result you would expect would be all zero values in sheet
2, correct?

Alan

wrote:
Stuart,

It should not be deleting any lines. The macro runs through column A of
sheet one. If the offsetting value in column B is 0, it looks for a
replacement from sheet 2. Otherwise it moves to the next row. What data
is being deleted that should not be?

I will be in a meeting for a few hours, but will try to check back
before the end of the day.

Alan

Stuart wrote:
Hi Alan,

I cant get your macro to work,

It is deleting some lines out

I did an example with this data,

In Sheet 1 :

ABCD 0 0 0
BBCD 0 0 0
CBCD 0 0 0
DBCD 0 0 0
asasasa 1234 1234 1234
asas 1234 1234 1234
aasa 1234 1234 1234
asasas 1234 1234 1234
asasasa 1234 1234 1234
asasa 1234 1234 1234
EBCD 0 0 0
FBCD 0 0 0

In sheet 2 :

ABCD 1234 1234 1234 1234 1234 1234
BBCD 1234 1234 1234 1234 1234 1234
CBCD 1234 1234 1234 1234 1234 1234
DBCD 1234 1234 1234 1234 1234 1234
EBCD 1234 1234 1234 1234 1234 1234
FBCD 1234 1234 1234 1234 1234 1234

I want the macro to compare the value contained in cell within column A
in Sheet 1 with the values of column A sheet 2.

If the value found in sheet 1 column A matches column A in sheet 2 then
replace the entire row in sheet 2 with the row in sheet 1.

Are you able to do this?

Thanks

Stuart

wrote:
Stuart,

I should have added this to my previous response. The line with the
error and the line above it should be on one line in your module.
Otherwise, you can use the following in place of those two lines. Post
back if you still have a problem.

Alan

.Range(Myfind.Address).EntireRow.Copy _
Destination:=Range(MyCell.Address)


Stuart wrote:
Still hasnt worked at all,

Do you think there is something I'm not doing?

Thanks

Stuart


wrote:
Be sure your copy and paste from the post did not insert an unwanted
line break in the code. I had another post out here and that was the
problem

Stuart wrote:
Hi Alan,

The code is highlighting

Destination:=Range(MyCell.Address) in red.

Trying to run the macro however it wont work at all.

Hope you can help,

Thanks

Stuart

wrote:
Stuart,

Place this in the worksheet module of the sheet you wish to replacing
values. It ran fine on small sample of info on two sheets. The coding
assumes you have a workbook with sheet2 being the update values you are
searcing for. We can modify if you have any issues.

Alan


Sub Updatelist()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange = Range("A1:A" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Offset(0, 1).Value < 0 Then
GoTo MoveOn:
Else:
'On Error Resume Next
Set Myfind = MyRange2.Find(What:=MyCell.Value, LookIn:= _
xlValues)
With Sheets(2)
.Range(Myfind.Address).EntireRow.Copy
Destination:=Range(MyCell.Address)
End With
End If
MoveOn:
Next
End Sub


Stuart wrote:
hi there,

yes codes do appear more than one,

Its a report which contains codes and values,

we now have reports which come from two systems at the moment, the code
will appear more than once (i believe, will check tomorrow)

There should be a replacement,

the new reports from the new system will have different codes however I
will have an underlying mapping table to rectify that,

so there should always be data, if column A in Sheet 1 contains the
same code in column A sheet 2 then replace the row, if not then ignore
it and move onto the next row,

now i dont actually no what whether the old report will show a zero in
column be next to the migrated funds hence im looking for a comparison
between column A sheet 1 and column A sheet 2. seems the most logical
way.

Thanks for a prompt response,

With the sounds of it i might be able to get all this cleared up
tonight!!!

Your a star if you can help

thanks

stuart

wrote:

This helps. So if I have two sheets with the following information:

Sheet1:

Code Amount
ABC 12.25
DEF 0
GHI 15.50
JKL 0
MNO 0

Sheet 2:

Code Amount
DEF 10.75
JKL 20.45

DEF and JKL on sheet1 are replaced with the updated values, correct?
Is the MNO case possible; that is, a zero on sheet 1 with no
replacement on sheet 2? If so, would you delete it or keep it? Also,
are the codes on each sheet unique? Can codes appear more than once on
each sheet?


wrote:
hi alan,

many thanks for getting back to me,

I will give you an example of what i'm trying to do, very very
confusing for me,

i have to write everything down on paper to work it all out!!! hahaha

I have one system which gives me a report which is imported into excel
in one sheet, in column A of this sheet we have a code, in B we have an
amount,

now here is the difficult part, some of the codes in column A will have
zero in column b cells, the majority will contain a value. Those that
do not contain a value are taken from another sheet contain the same
column names etc but will only contain information for those codes that
contain zero in sheet 1.

I want a macro to be able to compare the two sheets and for those cells
in column A sheet one that match column A in sheet 2 to replace the
full row in sheet 1 which the full row in sheet 2.

Does this make sense to you?

Its so confusing,

Hope you can help,

Kind Regards,

Stuart
wrote:
Stuart,

A few questions. Is the word unique on the "to be replaced" list? That
is, are you looking to replace the first instance of the found value or
all instances in the list to be updated? If it is found a second time
on the first list, does it overwrite the first replacement on list 2?

Alan

wrote:
Hi there,

I wondered if someone could help with a macro creation, I'm a novice at
this but hope someone can help.

I have a sheet within a spreadsheet containing a large volume of data,

What I want to do is if a row within a sheet contains a word that is
the same as a word contained in another sheet it will copy the row into
the original sheet replacing the contents of the row with the new data.

If you need further info then please get back to me,

I'll take whoever manages to help me out to the pub because this has
been a nightmare!!!!

Thanks in advance

Stuart