Hi RS,
The original error message says that "Worksheet" is not used properly. As
someone already suggested, it must be "Worksheets" .
If you correct this, you then get another error. To identify the cause, I
would firstly change the silly sheetname to something easier, say
"Testsheet", i.e. rename the sheet and change the command to
For Each Cell In Worksheets("Testsheet").Range("rngData")
My guess is that you will still get an error. I think you do not have a
named range "rngdata" (no quotes) on your sheet. If rngData is a variable
containing the name of the range, you need to remove the quotes, i.e.
For Each Cell In Worksheets("Testsheet").Range(rngData)
Hope this helps.
--
Gerd
"RS" wrote:
Sorry for posting this again, but Ive yet to receive a solution to my new
post 3 days ago (Find & Replace and Find & Insert macro help needed).
Sohere is another attempt at getting this answered. 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
find/replace items in my code, anything else I added turned a red color (see
the link below for what I was attempting). Someone suggested that I try
putting my find/replace values on a separate worksheet in 2 columns, giving
the find values in the first column a name, 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 Module3):
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). From my post 3 days ago,
someone suggested that I make Worksheet plural. 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.
I dont know how to fix this. One other request, how would I modify the
code to limit the replace to a certain column?