Home>

,
worksheet "Registration"

August Row 2 Co2 emissions 19.95 power consumption 38.52 TOTAL 55.83

worksheet "Emissions by year"

2020 April May June July August
Usage time (h) 150.17 86.21 96.42 53.4

I want to copy the TOTAL value 55.83 from the registration sheet above and paste it in August of the emission sheet by year below.
I would be grateful if you could teach me because I have reached a dead end.

Sub yotei()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Lastrow As Integer
    Dim kensaku As Range
    Dim myrange As Range

    Worksheets("Register").Activate
    Set ws1 = Worksheets ("Emissions by year")
    Set ws2 = Worksheets ("Register")

    Lastrow = ws1.Range("a1").End(xlToRight).Column'Get the last column of column A
    ws2.Range("a4").Activate'activate a4 cell

    Set myrange = ws1.Range("A1:a"&lastrow)'Get the last column of A1:A cell
    Set kensaku = myrange.Find(what:=ActiveCell, lookat:=xlPart)'Find to find if the same value as Activecell is in myrange and assign it to kensaku
    ActiveCell.Offset(0, 7).Copy
    kensaku.Offset(1, 1).PasteSpecial *I get an error here. (Code 91)
    'End If

    'Loop
End Sub


I searched variously on the net, but I am troubled because I do not understand why it causes an error.

  • Answer # 1

    Although it is as follows

    Lastrow = ws1.Range("a1").End(xlToRight).Column'Get the last column of column A

    This part is searched not to the last row of column A but to the right starting from A1.
    Last row indicates that the 6th column (F) is the maximum.

    Also, Set myrange = ws1.Range("A1:a"&lastrow)'get the last column of A1:A cell

    The part of is supposed to be processed toward the bottom of column A.

    The data for the worksheet "Emissions by year" is not horizontal, but horizontal
    I don't know why I want to get the maximum row in column A.

    As a result, the specification of FIND will be messed up, so it is better to organize the information a little.

    Since the number of data is small, first of all, please use the method without FIND as follows.
    I think that FIND can be processed by understanding how to specify data.

    The reference level is fine. ..

    Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Lastcoumns As String
    Dim i As Double
    Dim key As String
    Dim mymonth As String
    Dim mystr As String
    Set ws1 = Worksheets ("Emissions by year")
    Set ws2 = Worksheets ("Register")
    Lastcoumns = ws1.Range("a1").End(xlToRight).Column
    key = ws2.Range("A1").Value
    mystr = ws2.Range("H1").Value
    For i = 1 To Lastcoumns
        mymonth = ws1.Cells(1, i).Value
        If mymonth = key Then
            ws1.Cells(2, i).Value = mystr
        End If
    Next
    Set ws1 = Nothing
    Set ws2 = Nothing
    End Sub


    I will upload it to the data service. The download period is 2 days.
    Please download if necessary.

    https://www.datadeliver.net/receiver/file_box.do?fb=9a132233876d4b4db2693b816cca2ac0&rc=001cb174404346c687b9197920f7c4f7&lang=en

    Also, it is the questioner's task to process the table vertically
    It may be the format you want to use with FIND!

    ADMoon Usage time
    2020 January XX
    2020 February XX
    2020 March XX
    2020 April XX
    2020 May XX
    2020 June XX
    2020 July XX
    2020 August 55.83

  • Answer # 2

    http://officetanaka.net/excel/vba/error/execution_error/error_91.htm

    If you get an error, try searching by code number or error message itself.
    Most of the time, some of you are asking the same questions so you can find the cause or solution.
    In this case,
    When an error occurs and I return to the VBE screen with the debug button,
    Check the contents of the variable in the local window.

    Is the content of the variable "kensaku" set to Nothing?
    The searched character string and the character string you want to find are
    Like the difference between full-width and half-width
    Invisible characters (spaces, line breaks in cells, etc.) are stuck together, so it may not be found.
    Try to find out with a manual search.
    The Find method just calls the operation, so the behavior is almost the same.
    The Find method also inherits the manual option changes, so
    Omitting the description of many settings may cause malfunction.
    (There is a possibility that the expected result will be returned or not returned and the operation will become unstable.)
    So it is recommended to use the code obtained from recording the macro as is.
    You can omit it if you are confident that it is definitely okay.

    In the same way, ActiveCell also changes, so
    It is safer not to use it if possible.
    I think that there is no mistake because I activated it just before,
    I'm a little scared.

    I would do the following:

    Sub Scheduled posting 2()
        Dim rngFrom As Range
        Dim rngTo As Range
        Dim ixCol As Variant
        With Worksheets("Register").UsedRange
            Set rngFrom = .Rows(.Rows.Count)
        End With
        Set rngTo = Worksheets("Emissions by year").Rows(1)
        ixCol = Application.Match(rngFrom.Cells(1).Value, rngTo, 0)
        If Not IsError(ixCol) Then
            rngTo(2, ixCol).Value = rngFrom.Cells(rngFrom.Cells.Count).Value
        Else
            MsgBox "The item to write to was not found."
        End If
    End Sub

    FYI.
    I have not confirmed the operation.
    The positional relationship of cells and the specification of the sheet are incorrect,
    If it doesn't work because of your misunderstanding, please tell me again.
    Also, if you have any questions or concerns, please ask.
    I don't know what I know or don't know, so
    I will omit the explanation for now.