Find & Replace macro help needed

[RS]

Sorry for posting this again, but I’ve 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/com...&p=1&tid=92d12b16-3316-4045-8a92-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 don’t know how to fix this. One other request, how would I modify the
code to limit the replace to a certain column?

 

[gerdmain]

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.

 

[RS]

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, I’m 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 doesn’t 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]

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

 

[RS]

Dear Gerd & other experts in this Excel Programming community,

I made the suggested changes but I’m 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? Everyone’s help is
welcome. Thanks.

 

[JLatham]

RS,
See if either of these routines doesn't help you some. I think the basic
problem is that Excel doesn't know what "rngData" is referring to and that's
throwing the exception.

Two routines here - first would have you select the cells in the source area
(column E on a sheet in these samples) and then call the TestFromKeyboard
routine. Or if you want to simply grab everything in the source column, the
TestAutomated routine could be easily adapted to do it with no pre-selecting
the source range required.


Sub TestFromKeyboard()
'presumes you select all of the cells in the 'source' sheet first
'and that you run this code from a button or by calling this
'macro from that sheet
'
'so when you call this routine, Selection will refer to the group of cells you
'have previously selected on the first sheet.
'
Dim anyCell As Object
Dim anyRange As Range

Set anyRange = Worksheets("Codes").Range("AD:AD")
For Each anyCell In Selection
anyRange.Replace What:=anyCell.Value, _
Replacement:=anyCell.Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Set anyRange = Nothing ' release resources
End Sub

Sub TestAutomated()
'presumes that you run this code from a button or by calling this
'macro from the sheet with the source data on it
'
'this would create a range referring to your data on that sheet and
'then do the replacements on the Codes sheet
'
'
Dim sourceRange As Range ' will be source column on 1st sheet
Dim anyCell As Object ' will be cells in the sourceRange
Dim anyRange As Range ' will be column AD on Codes sheet
Dim LastCell As String

'lets just grab everything in a particular column,
'column E for this code example
'
'find last used cell in column E
'code to test if in Excel 2007 or earlier
If Val(Application.Version) < 12 Then
'pre Excel 2007
LastCell = Range("E" & Rows.Count).End(xlUp).Address
Else
'Excel 2007
LastCell = Range("E" & Rows.CountLarge).End(xlUp).Address
End If
Set sourceRange = ActiveSheet.Range("E1:" & LastCell)

Set anyRange = Worksheets("Codes").Range("AD:AD")
For Each anyCell In sourceRange
anyRange.Replace What:=anyCell.Value, _
Replacement:=anyCell.Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Set anyRange = Nothing ' release resources
End Sub

 

[JLatham]

After some off-line discussion the following code was created to accomplish
the task in Excel 2000:

Sub TestAutomated()
'presumes that you run this code from a button or by calling this
'macro from the sheet with the source data on it
'
'this would create a range referring to your data on that sheet and
'then do the replacements on the Codes sheet
'
Dim sourceRange As Range ' will be source column on 1st sheet
Dim anyCell As Object ' will be cells in the sourceRange
Dim anyRange As Range ' will be column AD on Codes sheet
Dim LastCell As String

' Set sourceRange = ActiveSheet.Range("H1:" & LastCell)
'you could change the line above to for your real-world use
' Set sourceRange = ActiveSheet.Range("H22:H41")
'you can try the following and see if it helps with the name thing,
'when referring to ranges that are named ranges, they have to be
'within "" marks - it does work in this workbook under Excel 2000
'named range rngData refers to Codes!$H$22:$H$41
Set sourceRange = Worksheets("Codes").Range("rngData")
'
Set anyRange = Worksheets("TestSheet").Range("AD:AD")
For Each anyCell In sourceRange
anyRange.Replace What:=anyCell.Value, Replacement:=anyCell.Offset(0,
1).Value, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next
Set anyRange = Nothing ' release resources
Set sourceRange = Nothing ' release resources
End Sub