#1
|
|||
|
|||
Excel VBA function range.replace
Hi everybody, I have a small issue with the fonction Range.Replace. I have the following code: Columns("F:H").Select Selection.Replace What:="Dec", Replacement:="Dez", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True Selection.Replace What:="Oct", Replacement:="Okt", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True Selection.Replace What:="May", Replacement:="Mai", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True Selection.Replace What:="Mar", Replacement:="Mär", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True Selection.Replace What:="-", Replacement:=".", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True Everything works well except when I replace "-" with "." all "-" are replaced by a "." in the complete sheet instead of only in the range. I tried as well to replace Columns("F:H").Select by Range(Cells(2, 6), Cells(Last_Row_Import, 8)).Select but still the same. did someone already face this issue? thank you for your support. |
#2
|
||||
|
||||
Perhaps you have merged cells? Avoid selecting anyway:
Code:
with Columns("F:H") .Replace What:="Dec", Replacement:="Dez", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True .Replace What:="Oct", Replacement:="Okt", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True .Replace What:="May", Replacement:="Mai", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True .Replace What:="Mar", Replacement:="Mär", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True .Replace What:="-", Replacement:=".", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True End With |
#3
|
|||
|
|||
Thank you Debaser.
Yesterday it was working perfectly but now it doesn't work anymore. to explain a little bit more, I replace "-" by "." to converter after cells as date. If I keep the text "07-Jan-2019 15:36" Excel is not able to modify as "07 JAN 2019". |
#4
|
||||
|
||||
@thomas
please wrap code with code tags (#). Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
||||
|
||||
re:" in the complete sheet instead of only in the range".
This happens when a single cell is effectively being operated on by Replace, so I'm just wondering whether somehow that's what's happening here; are columns F:H in a row merged cells? Is there only one row that it's operatimng on because there's only vone line of data? Could you make up a file where you have this happening, but attach it here in a state before any replacing has been done? It will make it a lot easier to investigate. |
#6
|
|||
|
|||
Attached is the file before any modification.
I did several test with the same file. sometime it works, sometine it doesn't. "ID" column should not be modified. |
#7
|
||||
|
||||
try adding one line before your With..End With block:
Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues) With Columns("F:H") .Replace What:="Dec", Replacement:="Dez", LookAt:=xlPart, .... and report back. |
#8
|
|||
|
|||
Thanks a lot p45cal.
I did several test with different files and it works well. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using IF Function where logical test is for range of values | guciolino | Excel | 10 | 02-13-2017 08:05 AM |
Help with Index Function to Select Range of Cells | bretyuin | Excel | 1 | 02-24-2016 05:11 AM |
Using Left function to format a range | USAOz | Excel | 4 | 09-10-2015 03:00 AM |
Find and Replace using Excel range | dmarie123 | Word VBA | 15 | 04-02-2013 07:54 AM |
Find and Replace within range | anil3b2 | Word VBA | 3 | 12-01-2010 02:35 AM |