Home>

Excuse me for the basic question.
I want to change the "serial value" of the garbled date to the date of the era.
If it starts with the era, it is not processed, and if it is a serial value, I want to change the date of the era and replace the cell value.
below
ActiveCell.Value = ipt'This is the problem.
So, the active cell is not replaced with "February 26, 2015", it remains the serial value.
Looking at the value of the above formula when the processing moves to "If InStr (1, ipt," Ming ") = 1 Or ..." in the VBA step-in,
The left side is the serial value of 38683, and the right side is November 27, 2005 converted by Format (ipt, "ggge year mm month dd day").
"38683" should be replaced with "November 27, 2005", but it has not changed.
Thank you.

Sub Macro1 ()
Dim ipt As String

'Obtain the date and delete (1860/6/19) etc. (Old calendar processing)
Range ("F1"). Select
Number of new cases = 10
ActiveCell.Offset (new number +1, 0) .Range ("A1"). Select

For line = new number + 1 To 2 Step -1
ActiveCell.Offset (-1, 0) .Range ("A1"). Select
ipt = ActiveCell.Value
'
If ipt = "" Then GoTo 99
If InStr (1, ipt, "light") = 1 Or InStr (1, ipt, "large") = 1 Or InStr (1, ipt, "Akira") = 1 Or InStr (1, ipt, "flat") = 1 Or InStr (1, ipt, "decree") = 1 Then GoTo 99

'Change the "serial value" of the date to the date of the era 2020/11/21 changed

ipt = Format (ipt, "ggge year mm month dd day")
ActiveCell.Value = ipt'This is the problem. The active cell does not replace "February 26, 2015".

If InStr (1, ipt, "light") = 1 Or InStr (1, ipt, "large") = 1 Or InStr (1, ipt, "Akira") = 1 Or InStr (1, ipt, "flat") = 1 Or InStr (1, ipt, "decree") = 1 Then GoTo 99 Else

End If
99 Next line

  • Answer # 1

    I want to change the "serial value" of the garbled date to the date of the era.

    If the serial number is displayed as is, the cell format is standard.

    If InStr (1, ipt, "light") = 1 Or InStr (1, ipt, "large") = 1 Or InStr (1, ipt, "Akira") = 1 Or InStr (1, ipt, "flat") = 1 Or InStr (1, ipt, "decree") = 1 Then GoTo 99


    If there is no problem with the above judgment, it means that the cells that are not subject to change are in the character string format, so change the format of the cells to the character string to unify them.

    ActiveCell.NumberFormatLocal = "@"
    ActiveCell.Value = ipt

  • Answer # 2

    First, let's understand what serial numbers are and how Excel manages dates.

    What is the Excel serial number? Let's understand how date management works

    This means that what you see is different from what is actually stored in the cell.

    Conversely, if you enter a value that can be interpreted as a date in the cell, it will be automatically converted to a serial number and stored.
    For example, enter "November 22, Reiwa 2" in the cell, and then in the Immediate window,

    ? CLng (ActiveCell.value)


    Try typing and pressing Enter.
    44157
    And the serial value are displayed.

    Once you understand that, what you want to do is
    Do you want to display the date in the cell in the Japanese calendar?
    Do you want the cell value itself to be a Japanese calendar string instead of a date?
    Let's clarify which one.

    On Excel, it is easier to handle the date as a serial number, and there is no merit in converting the date to a character string.

    Therefore, it is recommended to display the serial number in the Japanese calendar by formatting.
    In VBA, it is possible with the following one line of code.

    ActiveCell.NumberFormatLocal = "ggge year m month d day"

    If you store it in date format (serial value),

    ? ActiveCell.Value'Standard date format
    2020/11/22
    ? CLng (ActiveCell.Value)'Serial value
     44157
    ? CLng (ActiveCell.Value)'Displayed format
    November 22, 2nd year of Reiwa


    You can freely retrieve the value,
    You can also sort and calculate the number of days elapsed.

    If you format it as a string, you can't do that.