Find and replace cell value macro help pls
Solved/Closed
Related:
- Find and replace cell value macro help pls
- Excel blinking cell without macro ✓ - Forum - Excel
- Replace function in Macro ✓ - Forum - Excel
- Replace cell if value of cell offset ✓ - Forum - Programming
- Replacing cell values ✓ - Forum - Office Software
- Insert last modified date in excel cell without macro ✓ - Forum - Excel
6 replies
rizvisa1
Aug 20, 2010 at 09:29 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Aug 20, 2010 at 09:29 AM
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")
=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")
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
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
Aug 20, 2010 at 09:47 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Aug 20, 2010 at 09:47 AM
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
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
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
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
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Aug 20, 2010 at 10:42 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Aug 20, 2010 at 10:42 AM
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.
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