Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,562
- Office Version
- 365
- 2016
- Platform
- Windows
I have a range of data in column A (A2:A15). They all contain numeric 8 digit values, the first 5 numbers representing a serial date and the remaining three a unique incremental number (001...015). A value would look like 44362001 ,,, 44362015. The values may not fill the row sequentially.
I am looking for a vba solution that will replace these values with a new preceeding 5 digits (serial date), and the sequence portion as "xxx". I would need to do this for all worksheets in the workbook. These values do not need to be numbers, they can be text. They are not used in any calculations and are just for referencing.
I ran into two issues as I used the macro recorder to get the code for this process. (Find 44362, replace with 44364 in the workbook). The code didn't appear to reference the need to find/replace in the workbook. Maybe it does, but I wasn't sure how to adapt it to my specific code.
This is what it gave me:
I adapted it to my specific code ...
I was getting an error with the '.cells' reference as highlighted in red.
The secode issue, is I have no idea how to replace the different trailing 3 digits to "xxx". If I had a working find and replace code, it would only make changes to the first 5 digits.
Thoughts.
I am looking for a vba solution that will replace these values with a new preceeding 5 digits (serial date), and the sequence portion as "xxx". I would need to do this for all worksheets in the workbook. These values do not need to be numbers, they can be text. They are not used in any calculations and are just for referencing.
I ran into two issues as I used the macro recorder to get the code for this process. (Find 44362, replace with 44364 in the workbook). The code didn't appear to reference the need to find/replace in the workbook. Maybe it does, but I wasn't sure how to adapt it to my specific code.
This is what it gave me:
VBA Code:
Sub Macro1()
' Macro1 Macro
Cells.Replace What:="44362", Replacement:="99999", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
I adapted it to my specific code ...
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "M1" Then
If IsDate(Target) = False Then
MsgBox "Please enter a valid date."
mbevents = False
Worksheets("Master").Range("M1") = dt_prvdate
Exit Sub
End If
dt_crtdate = Worksheets("Master").Range("M1").Value
sr_crtdate = CLng(dt_crtdate)
With ThisWorkbook
.Cells.Replace What:=sr_prvdate, Replacement:=sr_crtdate, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With
End If
End Sub
I was getting an error with the '.cells' reference as highlighted in red.
The secode issue, is I have no idea how to replace the different trailing 3 digits to "xxx". If I had a working find and replace code, it would only make changes to the first 5 digits.
Thoughts.