Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-28-2016, 06:37 PM
Rod_Bowyer Rod_Bowyer is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2007
Novice
Macro to Find and Replace Does Not Work - But Works Manually
 
Join Date: Mar 2016
Posts: 12
Rod_Bowyer is on a distinguished road
Default 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!
Attached Files
File Type: xlsx Example_For_Forum.xlsx (9.5 KB, 11 views)
Reply With Quote
  #2  
Old 03-28-2016, 08:45 PM
Philb1 Philb1 is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 03-29-2016, 12:25 AM
Rod_Bowyer Rod_Bowyer is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2007
Novice
Macro to Find and Replace Does Not Work - But Works Manually
 
Join Date: Mar 2016
Posts: 12
Rod_Bowyer is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 10-11-2018, 07:21 PM
nre nre is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2016
Novice
 
Join Date: Oct 2018
Posts: 3
nre is on a distinguished road
Default

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..
Reply With Quote
  #5  
Old 10-12-2018, 06:22 AM
NoSparks NoSparks is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 7 64bit Macro to Find and Replace Does Not Work - But Works Manually Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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 ?
Reply With Quote
  #6  
Old 10-14-2018, 06:12 PM
nre nre is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2016
Novice
 
Join Date: Oct 2018
Posts: 3
nre is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 10-14-2018, 11:45 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 7 64bit Macro to Find and Replace Does Not Work - But Works Manually 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

@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
Reply With Quote
  #8  
Old 10-14-2018, 11:49 PM
nre nre is offline Macro to Find and Replace Does Not Work - But Works Manually Windows 10 Macro to Find and Replace Does Not Work - But Works Manually Office 2016
Novice
 
Join Date: Oct 2018
Posts: 3
nre is on a distinguished road
Default

@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
Reply With Quote
Reply

Tags
find/replace macro not wk

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Find and Replace Does Not Work - But Works Manually 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
Macro to Find and Replace Does Not Work - But Works Manually 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:24 PM.


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