This question has 2 parts to it.

QUESTION 1: FIND & REPLACE (Follow-up from a previous post).
I'm using Excel 2000 and I ran into a problem when I tried making a macro
which did a find and replace on many items on an imported spreadsheet. After
I entered 13 items, anything else I added turned a red color. Someone had
suggested that I try putting my find/replace values on a separate worksheet
in 2 columns and then use coding to do the function. Since I'm new to VB
coding I tried what was suggested but couldn't get it to work properly.

The background for this originally was posted here under the title "Find &
Replace macro limit?":
http://www.microsoft.com/office/comm...2-3e58add3b711

Basically, I added a button and clicking on it is supposed to activate the
following macro (located in Module4):

Sub Button2_Click()

For Each Cell In Worksheet("import (MACRO test) (2)").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

However, I get a "Compile Error: Sub or Function not defined" and the word
"Worksheet" is highlighted. When I click OK, it also highlights the 1st line
of the macro in yellow (the name of the macro). What am I doing wrong?


QUESTION 2: FIND & INSERT.
a) How can I use a similar concept to automatically find companies on a
worksheet and insert the pay rate for these companies in a separate column on
that worksheet based on either:
1) 2 different columns of criteria on the same worksheet (plus the
company name), or
2) a pay rate table located, preferably, in a different workbook (a
separate Excel file)?

Keep in mind that there are many different criteria possible, so creating a
regular formula is out of the question for this task. Also, ...

b) Would it be possible that if there is more than one rate for any company
that Excel would provide a list of choices in the rate column for those
companies (like a drop down list of choices)?

I think the answer to Q1 might be that Worksheet should be Worksheets, plural.

You should probably try eating the rest of the elephant one bite at the time
by providing a proposed sheet layout for what data would be located where and
then ask how to do whatever it is you need to do with that data. Take it one
post at a time and you will probably get a quicker, better response.

"RS" wrote:

This question has 2 parts to it.

QUESTION 1: FIND & REPLACE (Follow-up from a previous post).
I'm using Excel 2000 and I ran into a problem when I tried making a macro
which did a find and replace on many items on an imported spreadsheet. After
I entered 13 items, anything else I added turned a red color. Someone had
suggested that I try putting my find/replace values on a separate worksheet
in 2 columns and then use coding to do the function. Since I'm new to VB
coding I tried what was suggested but couldn't get it to work properly.

The background for this originally was posted here under the title "Find &
Replace macro limit?":
http://www.microsoft.com/office/comm...2-3e58add3b711

Basically, I added a button and clicking on it is supposed to activate the
following macro (located in Module4):

Sub Button2_Click()

For Each Cell In Worksheet("import (MACRO test) (2)").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

However, I get a "Compile Error: Sub or Function not defined" and the word
"Worksheet" is highlighted. When I click OK, it also highlights the 1st line
of the macro in yellow (the name of the macro). What am I doing wrong?


QUESTION 2: FIND & INSERT.
a) How can I use a similar concept to automatically find companies on a
worksheet and insert the pay rate for these companies in a separate column on
that worksheet based on either:
1) 2 different columns of criteria on the same worksheet (plus the
company name), or
2) a pay rate table located, preferably, in a different workbook (a
separate Excel file)?

Keep in mind that there are many different criteria possible, so creating a
regular formula is out of the question for this task. Also, ...

b) Would it be possible that if there is more than one rate for any company
that Excel would provide a list of choices in the rate column for those
companies (like a drop down list of choices)?

I made Worksheet plural, but now I get a €œRun-time Error €˜1004
Application-defined or object-defined error. Clicking Debug highlights the
first line of the code in yellow. Also, how would I modify the code to limit
the search to a certain column?

Before, making a new post for the question 2, I can provide a little more
info about where the data might be located. Lets say that row 1 has headers
in it. Column J is €œType€�, column K is €œModel€�, column L is €œCompany€� and
column M is where I would want the rates to appear. The rates would be
listed on another worksheet in the same workbook or in another workbook
entirely. So€¦how would the code look if it used those 3 columns (J,K, & L)
as its criteria for filling in the rates? Hope this extra info helps.


"JLGWhiz" wrote:

I think the answer to Q1 might be that Worksheet should be Worksheets, plural.

You should probably try eating the rest of the elephant one bite at the time
by providing a proposed sheet layout for what data would be located where and
then ask how to do whatever it is you need to do with that data. Take it one
post at a time and you will probably get a quicker, better response.

"RS" wrote:

This question has 2 parts to it.

QUESTION 1: FIND & REPLACE (Follow-up from a previous post).
I'm using Excel 2000 and I ran into a problem when I tried making a macro
which did a find and replace on many items on an imported spreadsheet. After
I entered 13 items, anything else I added turned a red color. Someone had
suggested that I try putting my find/replace values on a separate worksheet
in 2 columns and then use coding to do the function. Since I'm new to VB
coding I tried what was suggested but couldn't get it to work properly.

The background for this originally was posted here under the title "Find &
Replace macro limit?":
http://www.microsoft.com/office/comm...2-3e58add3b711

Basically, I added a button and clicking on it is supposed to activate the
following macro (located in Module4):

Sub Button2_Click()

For Each Cell In Worksheet("import (MACRO test) (2)").Range("rngData")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

However, I get a "Compile Error: Sub or Function not defined" and the word
"Worksheet" is highlighted. When I click OK, it also highlights the 1st line
of the macro in yellow (the name of the macro). What am I doing wrong?


QUESTION 2: FIND & INSERT.
a) How can I use a similar concept to automatically find companies on a
worksheet and insert the pay rate for these companies in a separate column on
that worksheet based on either:
1) 2 different columns of criteria on the same worksheet (plus the
company name), or
2) a pay rate table located, preferably, in a different workbook (a
separate Excel file)?

Keep in mind that there are many different criteria possible, so creating a
regular formula is out of the question for this task. Also, ...

b) Would it be possible that if there is more than one rate for any company
that Excel would provide a list of choices in the rate column for those
companies (like a drop down list of choices)?