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

(E-Mail Removed) 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
(E-Mail Removed) 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
>
> (E-Mail Removed) 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?


(E-Mail Removed) 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
> (E-Mail Removed) 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
> >
> > (E-Mail Removed) 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

(E-Mail Removed) 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?
>
>
> (E-Mail Removed) 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
> > (E-Mail Removed) 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
> > >
> > > (E-Mail Removed) 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
>
> (E-Mail Removed) 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?
> >
> >
> > (E-Mail Removed) 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
> > > (E-Mail Removed) 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
> > > >
> > > > (E-Mail Removed) 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

(E-Mail Removed) 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
> >
> > (E-Mail Removed) 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?
> > >
> > >
> > > (E-Mail Removed) 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
> > > > (E-Mail Removed) 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
> > > > >
> > > > > (E-Mail Removed) 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
>
> (E-Mail Removed) 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
> > >
> > > (E-Mail Removed) 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?
> > > >
> > > >
> > > > (E-Mail Removed) 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
> > > > > (E-Mail Removed) 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
> > > > > >
> > > > > > (E-Mail Removed) 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


(E-Mail Removed) 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
> >
> > (E-Mail Removed) 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
> > > >
> > > > (E-Mail Removed) 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?
> > > > >
> > > > >
> > > > > (E-Mail Removed) 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
> > > > > > (E-Mail Removed) 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
> > > > > > >
> > > > > > > (E-Mail Removed) 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
>
>
> (E-Mail Removed) 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
> > >
> > > (E-Mail Removed) 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
> > > > >
> > > > > (E-Mail Removed) 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?
> > > > > >
> > > > > >
> > > > > > (E-Mail Removed) 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
> > > > > > > (E-Mail Removed) 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
> > > > > > > >
> > > > > > > > (E-Mail Removed) 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