Dear Gerd & other experts in this Excel Programming community,

I made the suggested changes but Im still getting the €œRun-time Error
€˜1004 Application-defined or object-defined error€�. Clicking Debug
highlights the first line of the code in yellow. I changed the
Worksheets("Testsheet") to Worksheets("Codes"). I thought that Worksheets
was specifying the sheet that I wanted to perform the replacements on, not
the location of my named range. I have a workbook with multiple sheets, and
I want to replace data on Testsheet only. I also want to limit the selection
to one column on Testsheet (column AD). Here is my current code:

Sub Button2_Click()

For Each Cell In Worksheets("Codes").Range(rngData)
Columns("AD").Replace What:=Cell.Value, Replacement:=Cell.Offset(0,
1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

Do I need to put €œDim Cell As Range€� after the €œSub Button2_Click()€� line?
Do I need to activate the sheet first? Should I change the code from
selecting all of column AD to something like €œRange(Range("AD1"),
Range("AD1").End(xlDown)).Select€�? If so, what would be the correct coding
for this? Would I need to add any other lines of code? Everyones help is
welcome. Thanks.


"gerdmain" wrote:

Hi,

You are right, the range does not have to be on the active sheet. However,
your statement says Worksheets("Testsheet").Range(rngData), which explicitely
says the range is on Testsheet. Remove the Worksheets("Testsheet"). -
including the dot - but I would use the correct sheetname "Codes" instead.

I was wondering what you might want to do with your current Replace. It
works on all CELLS of the sheet? If you want only a column (say col A = 1),
use
Columns(1).Replace
But you would normally specify the exact range like Range("A4:A94"), or if
you want to use the row and column numbers
Range(Cells(4,1),Cells(94,1)).Replace

Have fun



--
Gerd


"RS" wrote:

Thanks for your response. I did what you suggested (already had changed
Worksheet to Worksheets) and renamed the sheet to €œTestsheet€� and got rid of
the quotes for rngData. However, Im still getting the €œRun-time Error
€˜1004 Application-defined or object-defined error€�. Clicking Debug
highlights the first line of the code in yellow. Here is my current code
(located in Module3):

Sub Button2_Click()

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

End Sub

The rngData is defined and refers to a SEPARATE worksheet called Codes. My
understanding is that this range doesnt need to be on the same worksheet.
Am I right? Specifically, in the Define Name dialog box, there is only one
name in the workbook, rngData, and in the space under €œRefers to:€� this is
what is shown: =Codes!$H$22:$H$41. Additionally, how would I modify the code
to limit the replace to a certain column?


"gerdmain" wrote:

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).
So€¦here 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?