Automated Search and Replace; Sheet2 to Sheet1
I have a customer spreadsheet with over 5000 rows in it. Each week I get
information that needs to be updated (100s of rows). The new information contains Customer Number and Paydate. I manually search for the customer number, located in column C, and then update the PayDate, located in Column M. Is there any way I can write some VBA that will do a massive search and replace. I have the new info in a Text File, but I am thinking I could import it into Sheet2 with 2 columns, customerNumber, and PayDate. Then just search Sheet1 customer number, and when found replace paydate€¦ but I am new to Excel programming and have no idea of how to implement this. Is this even possible???? Thank you in advance for your time! Mike P |
Automated Search and Replace; Sheet2 to Sheet1
This code should get you started.
Sub test() With Sheets("Sheet2") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set CustNoRangeSh2 = _ .Range(.Cells(1, "A"), .Cells(LastRow, "A")) End With With Sheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row Set CustNoRangeSh1 = _ .Range(.Cells(1, "C"), .Cells(LastRow, "C")) For Each Cell In CustNoRangeSh1 Set c = CustNoRangeSh2.Find(what:=Cell, _ LookIn:=xlValues) If Not c Is Nothing Then PayDate = c.Offset(rowoffset:=0, _ columnoffset:=1).Value Cell.Offset(rowoffset:=0, _ columnoffset:=10).Value = PayDate Else MsgBox ("Cannot find Customer No = " & Cell) End If Next Cell End With End Sub "Mike P" wrote: I have a customer spreadsheet with over 5000 rows in it. Each week I get information that needs to be updated (100s of rows). The new information contains Customer Number and Paydate. I manually search for the customer number, located in column C, and then update the PayDate, located in Column M. Is there any way I can write some VBA that will do a massive search and replace. I have the new info in a Text File, but I am thinking I could import it into Sheet2 with 2 columns, customerNumber, and PayDate. Then just search Sheet1 customer number, and when found replace paydate€¦ but I am new to Excel programming and have no idea of how to implement this. Is this even possible???? Thank you in advance for your time! Mike P |
Automated Search and Replace; Sheet2 to Sheet1
A comment to Joel.
Don't you think that it would be better to pick up the customer number from sheet 2 and find them on sheet 1? The reason that I say this is Mike said he has 5000 records on sheet 1 and he has to make 100's of changes which suggests that there is less than 1000 to be checked. The code has a msgbox if not found and it could drive the user insane with some 4000+ not found. However, if there is a customer code on the new sheet then the user would want to know if it is not found in the master sheet and also the msgbox would not display if all found. Regards, OssieMac "Joel" wrote: This code should get you started. Sub test() With Sheets("Sheet2") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set CustNoRangeSh2 = _ .Range(.Cells(1, "A"), .Cells(LastRow, "A")) End With With Sheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row Set CustNoRangeSh1 = _ .Range(.Cells(1, "C"), .Cells(LastRow, "C")) For Each Cell In CustNoRangeSh1 Set c = CustNoRangeSh2.Find(what:=Cell, _ LookIn:=xlValues) If Not c Is Nothing Then PayDate = c.Offset(rowoffset:=0, _ columnoffset:=1).Value Cell.Offset(rowoffset:=0, _ columnoffset:=10).Value = PayDate Else MsgBox ("Cannot find Customer No = " & Cell) End If Next Cell End With End Sub "Mike P" wrote: I have a customer spreadsheet with over 5000 rows in it. Each week I get information that needs to be updated (100s of rows). The new information contains Customer Number and Paydate. I manually search for the customer number, located in column C, and then update the PayDate, located in Column M. Is there any way I can write some VBA that will do a massive search and replace. I have the new info in a Text File, but I am thinking I could import it into Sheet2 with 2 columns, customerNumber, and PayDate. Then just search Sheet1 customer number, and when found replace paydate€¦ but I am new to Excel programming and have no idea of how to implement this. Is this even possible???? Thank you in advance for your time! Mike P |
Automated Search and Replace; Sheet2 to Sheet1
I think one of the hardest point of answering Postings is the interpretation
of the questions. I may an assumption that may be wrong. I thought the update list contained all the accounts. Usually when you get a monthly report it contains all the active accounts. You may be right that it is just an update list where it would be better to do it your way.. "OssieMac" wrote: A comment to Joel. Don't you think that it would be better to pick up the customer number from sheet 2 and find them on sheet 1? The reason that I say this is Mike said he has 5000 records on sheet 1 and he has to make 100's of changes which suggests that there is less than 1000 to be checked. The code has a msgbox if not found and it could drive the user insane with some 4000+ not found. However, if there is a customer code on the new sheet then the user would want to know if it is not found in the master sheet and also the msgbox would not display if all found. Regards, OssieMac "Joel" wrote: This code should get you started. Sub test() With Sheets("Sheet2") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set CustNoRangeSh2 = _ .Range(.Cells(1, "A"), .Cells(LastRow, "A")) End With With Sheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row Set CustNoRangeSh1 = _ .Range(.Cells(1, "C"), .Cells(LastRow, "C")) For Each Cell In CustNoRangeSh1 Set c = CustNoRangeSh2.Find(what:=Cell, _ LookIn:=xlValues) If Not c Is Nothing Then PayDate = c.Offset(rowoffset:=0, _ columnoffset:=1).Value Cell.Offset(rowoffset:=0, _ columnoffset:=10).Value = PayDate Else MsgBox ("Cannot find Customer No = " & Cell) End If Next Cell End With End Sub "Mike P" wrote: I have a customer spreadsheet with over 5000 rows in it. Each week I get information that needs to be updated (100s of rows). The new information contains Customer Number and Paydate. I manually search for the customer number, located in column C, and then update the PayDate, located in Column M. Is there any way I can write some VBA that will do a massive search and replace. I have the new info in a Text File, but I am thinking I could import it into Sheet2 with 2 columns, customerNumber, and PayDate. Then just search Sheet1 customer number, and when found replace paydate€¦ but I am new to Excel programming and have no idea of how to implement this. Is this even possible???? Thank you in advance for your time! Mike P |
All times are GMT +1. The time now is 12:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com