Home>

Use the Split function to convert the value (date) obtained from another book with Excel VBA to the sheet of the book.
I want to paste it.

I succeeded once, but from the second time I started to fail.

Run-time error 9
The index is out of scope.
Corresponding source code
  Dim i As Variant
    Dim tmp
        For i = 6 To Sheets (2) .Cells (Rows.Count, 1) .End (xlUp) .Row + 1
        tmp = Split (Cells (2, i), "/")
            If Sheets ("sheet2"). Range ("F"&i) .Value<>"" Then
                Sheets ("sheet3"). Range ("C30"). Value = tmp (0)&"year"
                Sheets ("sheet3"). Range ("D30"). Value = tmp (1)&"Month"
                Sheets ("sheet3"). Range ("E30"). Value = tmp (2)&"day"
            End If
        Next
Supplementary information (FW/tool version, etc.)

Excel 2010

  • Answer # 1

    Are Cells (2, i) blank or a value that does not include "/"?

    Sheets (2) .Cells (Rows.Count, 1) .End (xlUp) .Row + 1
    I'm getting the next line after the last line, but it's blank, isn't it?

    after,
    Split (Cells (2, i), "/")
    The part of, i should be the row number, but I specified it as the column number, is that okay?

    Let's execute step by line and check if it works as expected. (Debugging work)

    How to debug VBA

  • Answer # 2

    Isn't it here?

    'tmp = Split (Cells (2, i), "/")
    tmp = Split (Cells (i, 2), "/")


    Also, since 30 of C to E is specified, is it okay to overwrite it more and more?


    If it is date data instead of a character string, you can write it as follows.

    If Sheets ("sheet2"). Range ("F"&i) .Value<>"" Then
        Sheets ("sheet3"). Range ("C30"). Value = Year (Cells (i, 2))&"Year"
        Sheets ("sheet3"). Range ("D30"). Value = Month (Cells (i, 2))&"Month"
        Sheets ("sheet3"). Range ("E30"). Value = Day (Cells (i, 2))&"Day"
    End If

  • Answer # 3

    Hello.
    Please refer to the answers of hatena19 and radames1000 for the cause of the error.

    Below are the impressions of seeing the VBA code.

    Coding that omits book and sheet objects stands out.
    This is a common tendency for beginners.
    Write book and sheet objects without the hassle.
    You may specify with With.

    When writing VBA code that handles complicated processing or a large number of books and sheets, it becomes difficult to determine which sheet in which book is in the code, which is a source of problems. (It also takes effort to analyze defects)