Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-21-2019, 03:37 AM
thomas47 thomas47 is offline Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2010 64bit
Novice
Excel VBA function range.replace
 
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 Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
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 Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2010 64bit
Novice
Excel VBA function range.replace
 
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 Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@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
Reply With Quote
  #5  
Old 03-23-2019, 12:28 PM
p45cal's Avatar
p45cal p45cal is offline Excel VBA function range.replace Windows 10 Excel VBA function range.replace Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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 Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2007
Novice
Excel VBA function range.replace
 
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, 8 views)
Reply With Quote
  #7  
Old 03-25-2019, 03:09 AM
p45cal's Avatar
p45cal p45cal is offline Excel VBA function range.replace Windows 10 Excel VBA function range.replace Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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 Excel VBA function range.replace Windows 7 64bit Excel VBA function range.replace Office 2007
Novice
Excel VBA function range.replace
 
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
Excel VBA function range.replace Using IF Function where logical test is for range of values guciolino Excel 10 02-13-2017 08:05 AM
Excel VBA function range.replace Help with Index Function to Select Range of Cells bretyuin Excel 1 02-24-2016 05:11 AM
Excel VBA function range.replace Using Left function to format a range USAOz Excel 4 09-10-2015 03:00 AM
Excel VBA function range.replace 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft