|
Find All Replace All
I have a database that lists rep companys by the zip codesthey represent.
Therefore, all companies and their information are entered multiple times within the worksheet, which makes changing any piece of information very difficult. I have created a userform in which you enter a company name into a textbox (Company) and hit the find key to bring up the company info i a buch of other textboxes on the userform. I then have a save button which takes any changes made to the company info and inserts them into the database. The problem I have is my code only finds the first incident of where the company is entered and changes that. I need the code for the save button to find all enteries for the company entered in (Company) and to replace all information in each row where that company is enter. Here is my code for the save button , like I said it works for one entery but I need to find and replace all enteries. Thanks for any help I can get. Private Sub Save_Click() Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -2).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, -2).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -2).Value, RepNumber.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -1).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, -1).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -1).Value, SAPNumber.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 0).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 0).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 0).Value, RepName.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 1).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 1).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 1).Value, RepAddress.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 2).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 2).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 2).Value, RepCity.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 3).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 3).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 3).Value, RepState.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 4).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 4).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 4).Value, RepZipCode.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 5).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 5).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 5).Value, RepBusPhone.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 6).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 6).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 6).Value, RepFax.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 7).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 7).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 7).Value, RepEmail.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 8).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 8).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 8).Value, Regions.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 17).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 17).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 17).Value, Inclusions.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 18).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 18).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 18).Value, Exclusions.Value) End Sub |
Find All Replace All
|
Find All Replace All
OK. Have a look in the vba help index for FINDNEXT. There is a good example of how to find all occurrences. Go from there. If all else fails after you try send your file to my address below along with snippets of these messages and a clear explanation of what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "aintlifegrand79" wrote in message ... Don, I need for the code to find the company name in the worksheet and replace the information in the rest of the cells in the same row, which my code already does but I need it to find every entry for a company and replace the information in every row in which the company appears. I couldn't figure out how to do this from from your response, so I figured I would try and explain what I am looking to do a little better. Thanks for your help. "Don Guillett" wrote: Just look in the vba help index for REPLACE or record a macro while doing editreplace -- Don Guillett Microsoft MVP Excel SalesAid Software "aintlifegrand79" wrote in message ... I have a database that lists rep companys by the zip codesthey represent. Therefore, all companies and their information are entered multiple times within the worksheet, which makes changing any piece of information very difficult. I have created a userform in which you enter a company name into a textbox (Company) and hit the find key to bring up the company info i a buch of other textboxes on the userform. I then have a save button which takes any changes made to the company info and inserts them into the database. The problem I have is my code only finds the first incident of where the company is entered and changes that. I need the code for the save button to find all enteries for the company entered in (Company) and to replace all information in each row where that company is enter. Here is my code for the save button , like I said it works for one entery but I need to find and replace all enteries. Thanks for any help I can get. Private Sub Save_Click() Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -2).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, -2).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -2).Value, RepNumber.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -1).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, -1).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, -1).Value, SAPNumber.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 0).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 0).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 0).Value, RepName.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 1).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 1).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 1).Value, RepAddress.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 2).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 2).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 2).Value, RepCity.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 3).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 3).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 3).Value, RepState.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 4).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 4).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 4).Value, RepZipCode.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 5).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 5).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 5).Value, RepBusPhone.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 6).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 6).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 6).Value, RepFax.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 7).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 7).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 7).Value, RepEmail.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 8).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 8).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 8).Value, Regions.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 17).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 17).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 17).Value, Inclusions.Value) Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 18).Value = ReplaceAll(Sheet1.Columns(4).FindAll(Company.Value ).Offset(0, 18).Value, Sheet1.Columns(4).FindAll(Company.Value).Offset(0, 18).Value, Exclusions.Value) End Sub |
| All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com