Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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). 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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). 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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). 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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 "RS" wrote: 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). 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace macro help needed
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 "RS" wrote: 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). 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help needed to compile a macro (replace only 8 cells of a row fromnext raw if one cell has value of 45g) | Excel Discussion (Misc queries) | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
FIND and REPLACE characters needed | New Users to Excel | |||
FIND and REPLACE characters needed | Excel Worksheet Functions |