Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Find and replace cell value macro help pls [Solved/Closed]

Report
-
 jonny -
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

















6 replies

Answer 1 / 6
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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")
Answer 2 / 6

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
Answer 3 / 6
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Answer 4 / 6

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
Answer 5 / 6
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Answer 6 / 6

mate that works perfectly, thankyou very much!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!