A few words of thanks would be greatly appreciated.

Find and replace cell value macro

Latest update on August 23, 2010 at 05:56 AM by aakai1056 .

Find and replace cell value macro

Issue
Solution
Note

Issue

I have these 20 formulas (1 per stock code) in my spreadsheet that come from etrade
=PROStaticData(2, "mqg.ASX", "2010/08/10;2010/08/19;3;True;False", "10")

i want to be able to change the dates all at once, i have the 2 cells setup the values I want to look for are in M8 and M9 and i want to replace it with the values in L8 and L9 respectively and this works fine anywhere in the spreadsheet except in the above formula for some reason it won't look through the formula for it the following is the code I am using.

Sub theone() 


'Selection.Replace What:="ValueToFind", Replacement:=Range("A1").Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 
' 
Range("B1:B2").Select 
Selection.Copy 
Range("L8:L9").Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
Range("M8").Select 
Application.CutCopyMode = False 
Selection.Copy 
Range("L8").Select 
Application.CutCopyMode = False 
Selection.Copy 


Cells.Replace What:=Range("M8").Value, Replacement:=Range("L8").Value, LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
ReplaceFormat:=False 
Cells.Replace What:=Range("M9").Value, Replacement:=Range("L9").Value, LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
ReplaceFormat:=False 
Range("L8:L9").Select 
Application.CutCopyMode = False 
Selection.Copy 
Range("M8:M9").Select 
ActiveSheet.Paste 
End Sub

Solution

Here are few doubts in your macro

Example

Range("M8").Select 
Application.CutCopyMode = False 
Selection.Copy

You copy this value, but never paste it and same with next block with L8. May be code is not needed or maybe you forgot to paste. The reason you are not able to replace it because, the date that is in formula is in format YYYY/MM/DD. The date that you are searching for is not in that format. So you need to add one more search and replace where you search for date in YYYY/MM/DD format and replace in same

Cells.Replace What:=FORMAT(Range("M8").Value, "YYYY/MM/DD"), Replacement:=FORMAT(Range("L8").Value, "YYYY/MM/DD"), LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
ReplaceFormat:=False

Here is your re-worked stuff.

Sub theone()
Dim sDateFind As String
Dim sDateRep As String

    Range("B1:B2").Select
    Selection.Copy
    
    Range("L8:L9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, _
                            Operation:=xlNone, _
                            SkipBlanks:=False, _
                            Transpose:=False
    
    Range("M8").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    
    Range("L8").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    sDateFind = Range("M8").Value
    sDateRep = Range("L8").Value
    
    Cells.Replace What:=Range("M8").Value, _
                  Replacement:=Range("L8").Value, _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  MatchCase:=False, _
                  SearchFormat:=False, _
                  ReplaceFormat:=False
    
    Cells.Replace What:=Format(sDateFind, "yyyy/mm/dd"), _
                  Replacement:=Format(sDateRep, "yyyy/mm/dd"), _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  MatchCase:=False, _
                  SearchFormat:=False, _
                  ReplaceFormat:=False
    

    sDateFind = Range("M9").Value
    sDateRep = Range("L9").Value

    Cells.Replace What:=Range("M9").Value, _
                  Replacement:=Range("L9").Value, _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  MatchCase:=False, _
                  SearchFormat:=False, _
                  ReplaceFormat:=False
    
      Cells.Replace What:=Format(sDateFind, "yyyy/mm/dd"), _
                  Replacement:=Format(sDateRep, "yyyy/mm/dd"), _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  MatchCase:=False, _
                  SearchFormat:=False, _
                  ReplaceFormat:=False
                  
    Range("L8:L9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M8:M9").Select
    ActiveSheet.Paste
    
End Sub

Note

Solved by rizvisa1

Excel macro replace cell value
Excel macro find and replace - Best answers
Selection.replace - Best answers
Excel macro highlight cell ✓ - Forum - Excel
Excel macro increase cell value by 1 - How-To - Excel
Excel macro clear cell contents - How-To - Excel
Excel macro copy cells from one workbook to another - How-To - Excel
Excel macro find cell with value - How-To - Excel

This document, titled « Find and replace cell value macro », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Excel - A macro to fill in data automatically

getting the week number automatically