Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-21-2019, 03:37 AM
thomas47 thomas47 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Mar 2014
Location: France
Posts: 6
thomas47 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 03-21-2019, 07:02 AM
Debaser's Avatar
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 165
Debaser is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 03-22-2019, 03:21 AM
thomas47 thomas47 is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Mar 2014
Location: France
Posts: 6
thomas47 is on a distinguished road
Default

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".
Reply With Quote
  #4  
Old 03-22-2019, 08:51 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@thomas
please wrap code with code tags (#). Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 03-23-2019, 12:28 PM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
  #6  
Old 03-25-2019, 02:46 AM
thomas47 thomas47 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2014
Location: France
Posts: 6
thomas47 is on a distinguished road
Default

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.
Attached Files
File Type: xlsm test tc M.xlsm (13.2 KB, 1 views)
Reply With Quote
  #7  
Old 03-25-2019, 03:09 AM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
  #8  
Old 03-25-2019, 08:25 AM
thomas47 thomas47 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2014
Location: France
Posts: 6
thomas47 is on a distinguished road
Default

Thanks a lot p45cal.
I did several test with different files and it works well.
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 11:14 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft