find and replace cell value macro help plsSolved/Closed

Question

Hello, 



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 respectivly

and this works fine anywhere in the spreadsheet except in the above formula for some reason it wont 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

















Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

I am sure I am missing a point here. You have
=PROStaticData(2, "mqg.ASX", "2010/08/10;2010/08/19;3;True;False", "10") 

So instead of hard coded dates. why not use the cell adress ?

=PROStaticData(2, "mqg.ASX", L8 & ";" & L9 & ";3;True;False", "10")

jonny · Answer

hi there, thanks for the reply
i cant use those cell refrences because the code sequence below is sent to etrade and the etrade addin does not recognise cell values  
=PROStaticData(2, "mqg.ASX", L8 & ";" & L9 & ";3;True;False", "10")

so it has to be sent in this exact way
=PROStaticData(2, "mqg.ASX", "2010/08/10;2010/08/19;3;True;False", "10")


cheers

rizvisa1 · Answer

Well how about if you add one one more step

do this on a sheet, and then copy the sheet to a new sheet and pastespecial as values.

I guess one needs to see the sheet to tell you a better solution. Like i dont know what is "PROStaticData"

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc 
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note:  your data need not be to be real data but a good representative of how data looks like

jonny · Answer

ok i have uploaded  the sheet

http://wikisend.com/download/605656/Worksheet.xlsm

Prostatic is an excell addin function i use it to get DDE data from etrade 

hope that helps

rizvisa1 · Answer

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 may be 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

jonny · Answer

mate that works perfectly, thankyou very much!

Find and replace cell value macro help pls

6 replies

Subscribe To Our Newsletter!