Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace in multiple sheets
Hi, I am trying to find a specific text in a worksheet and then put a certaint text in cells next to where first text is found. And I want to do it in all worksheets in the file I have. Here is the code that I came up with but this only works on the active worksheet that I am in... I think it should work on all worksheets in the file. Does anyone know why it would do that... Sub macro2 Dim wks As Worksheet Dim txtlookup As String Dim txtchange As String txtlookup = InputBox("Enter Text to Lookup:", "Enter Text") txtchange = InputBox("Enter Text to change to:", "Enter Text") For Each wks In ThisWorkbook.Sheets For Each cell In Range("A1:A2000") If cell.Value = txtlookup Then cell.Offset(0, 1).Value = txtchange End If Next cell Next wks End Sub Thanks, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=568789 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace in multiple sheets
Range("A1:A2000")
will refer to the active sheet (if the code is in a general module). So try: For Each cell In wks.Range("A1:A2000") But you may want to record a macro when you do Edit|Replace. Then loop through all the sheets using that code. sa02000 wrote: Hi, I am trying to find a specific text in a worksheet and then put a certaint text in cells next to where first text is found. And I want to do it in all worksheets in the file I have. Here is the code that I came up with but this only works on the active worksheet that I am in... I think it should work on all worksheets in the file. Does anyone know why it would do that... Sub macro2 Dim wks As Worksheet Dim txtlookup As String Dim txtchange As String txtlookup = InputBox("Enter Text to Lookup:", "Enter Text") txtchange = InputBox("Enter Text to change to:", "Enter Text") For Each wks In ThisWorkbook.Sheets For Each cell In Range("A1:A2000") If cell.Value = txtlookup Then cell.Offset(0, 1).Value = txtchange End If Next cell Next wks End Sub Thanks, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=568789 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace in multiple sheets
Dave, Thanks. I will try your solution. Also, I found out that adding the following line after worksheet FOR statement does the trick too. wks.activate which really does the same thing that you are saying. Thanks Again. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=568789 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace in multiple sheets
Almost the same...
It actually activates that worksheet. wks.range(...) doesn't depend on the selection of sheets. And if the code is behind a worksheet, it still won't work correctly. It's not usually a good idea to select sheets, select ranges or activate workbooks. It just slows things down and makes it more difficult to debug the code (my opinion). sa02000 wrote: Dave, Thanks. I will try your solution. Also, I found out that adding the following line after worksheet FOR statement does the trick too. wks.activate which really does the same thing that you are saying. Thanks Again. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=568789 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find replace in multiple sheets based on namebox | Excel Discussion (Misc queries) | |||
Replace with Grouped Sheets | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
how do I do a global replace in all sheets in a workbook? | Excel Worksheet Functions | |||
Find / Replace sheets vs workbook in VB | Excel Worksheet Functions |