Find and replace cell value macro
Find and replace cell value macro
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
Related
- Excel macro replace cell value
- Excel macro find and replace - Best answers
- Selection.replace - Best answers
- Excel macro color cell based on value ✓ - Forum - Programming
- Excel macro insert picture based on cell value - Forum - Excel
- Excel macro to merge cells based on condition ✓ - Forum - Excel
- Excel macro send email to address in cell ✓ - Forum - Excel
- Excel - macro to lookup cell and add new row - 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).
Subscribe To Our Newsletter!
