#1
|
|||
|
|||
Macro to Find and Replace Does Not Work - But Works Manually
I have a spreadsheet into which I import web data. One of the columns is called "Grade". However, some of the data is imported as a short date. When I use the "Find and Replace" facility it finds all the "dates" and replaces them with the appropriate "Grade". However, when I record a Macro while doing this it works while I am recording the Macro but does not work when I run the Macro later.
The recorded Macro is:- Sub Macro2() ' ' Macro2 Macro ' This section of the macro replaces "dates" in the Grade column and replaces them with Grades. ' ' Keyboard Shortcut: Ctrl+a ' Application.Goto Reference:="R19C9" Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:="04-05-2016", Replacement:="'4/5", LookAt:=xlPart _ , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub I have copied just this column into a spreadsheet to show what it looks like. I have only used one "date" so far in this Macro. Normally this data is in Column I starting at row 19. Why this should work while I am recording it but not while I am running it is a frustrating mystery. I would very much appreciate any help ... yet again! |
#2
|
|||
|
|||
Hi Rod
With the example you gave the code below will change any cell with a date in the range on column I to 4/5 Using Replace will work for a range of cells with the same date, if there are different dates you will still have to loop through the dates. This is a start, if you want more help can you provide more detail & a better example etc Code:
Option Explicit Sub ReplaceDates() Dim Ws As Worksheet Dim Rcell As Range Dim sReplaceWith As String sReplaceWith = "4/5" Set Ws = ThisWorkbook.ActiveSheet For Each Rcell In Ws.Range(Ws.Cells(19, 9), Ws.Cells(Rows.Count, 9).End(xlUp)) If IsDate(Rcell) Then With Rcell .NumberFormat = "@" .Value = sReplaceWith End With End If Next Rcell End Sub |
#3
|
|||
|
|||
Changing "Dates" To Text
Hi Phil, I very much appreciate your reply.
You are obviously light years ahead of me with VBA coding. I think that the problem is with my bigger Macro - I am formatting the data first before I try to do the Find and Replace. Please hang fire until I have checked this out. Again - many thanks - Rod |
#4
|
|||
|
|||
That thread being from 2016, that's a long shot, but... Rod, did you find the reason of your problem?
I have the same exact situation here, on Office 2016 (365). A macro that used to perfectly work to replace a character: I import this macro in another file, it's not replacing anymore. Now, like you, if I do the "replace" by end, and record it as a macro, it works, and generates exactly the same code that I use in my macro. But if I rund this code again later from my macro, it doesn't work!! This part of the code is at the very beginning of my macro, nothing exotic precedes it. And the macro was perfectly working "as is" a few days ago.. |
#5
|
|||
|
|||
Hello nre,
Welcome to the forum. In order to assist, can you attach a sample file with the macro so we can test and see exactly what's happening or not happening ? |
#6
|
|||
|
|||
NoSparks, thank you for asking. I actually found the problem in between: the character I was looking to replace was "," (comma). The problem was coming from the fact that I ad moved to a new computer, and localization settings were slightly different.. On this new one byd efault "," was the decimal separator. Thus I guess it was not managed as a "normal" character by Excel. After changing the decimal separator to "." in my settings, the macro restarted to work fine.
|
#7
|
||||
|
||||
@nre
Hello and welcome in the future, please do not hijack existing threads but rather create one of your own with eventual link(s) to existing threads. Thank you
__________________
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 |
#8
|
|||
|
|||
@Pecoflyer
Hi! I did this hoping that it would notify the original poster in order to have his solution, sorry for that. Thank you both for your answers and tactfulness |
Tags |
find/replace macro not wk |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find and Replace doesn't work. | Bop70 | Word | 3 | 02-04-2015 11:45 AM |
Find and replace No longer work | TJH | Word | 3 | 03-25-2014 11:33 PM |
Find and Replace Macro | amparete13 | PowerPoint | 3 | 03-11-2014 05:29 AM |
macro or find/replace | JamesVenhaus | Word | 2 | 02-27-2012 03:34 PM |
Find and Replace Macro - A Better Way | Tribos | Word VBA | 0 | 10-08-2008 03:22 AM |