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