Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
search Sheet2! for the contents of Sheet1! | Excel Discussion (Misc queries) | |||
Checking the Cells in Sheet1 with Cells in Sheet2 and replace | Excel Worksheet Functions | |||
multiple search criteria to find and copy from sheet1 and paste into sheet2 | Excel Programming | |||
Search, find, copy from sheet1 and paste into sheet2 | Excel Programming |