Find All Replace All

[aintlifegrand79]

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

 

[Don Guillett]

Just look in the vba help index for REPLACE or record a macro while doing
edit>replace

 

[aintlifegrand79]

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]

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.