Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#11
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#13
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#14
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#15
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#16
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#17
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#18
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#19
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#20
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
macro to copy value from 2nd file, if a word found in a correspondingrow of both files | Excel Discussion (Misc queries) | |||
Copy rows based on cell content | Excel Discussion (Misc queries) | |||
Copy rows from one sheet to another based on a cell value | Excel Worksheet Functions | |||
copy rows based on cell value | Excel Programming | |||
Macro to copy cell data to word document based on an active row? | Excel Programming |