LoginSignup
@frswataru (本石 渉)

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

VBA 実行時エラー'1004'

Q&A

Closed

ExcelVBA

解決したいこと

VBA Excelで上手く変数が宣言出来ません
１．Book立上げで"Call Update"で関数呼び出し
2.Update()実行
3.エラーが発生
解決方法を教えて下さい。
スクリーンショット 2021-02-17 121018.jpg

発生している問題・エラー

VBA 実行時エラー'1004'

image.png

This Workbook

Private Sub Workbook_Open()
Dim wbk As Workbook
Set wbk = ThisWorkbook

    wbk.RefreshAll

Call Update

End Sub

Module1

Sub Update()
Dim wb As Workbook
Dim ws, ws1 As Worksheet
Set wb = ThisWorkbook

Set ws = Worksheets("元")
Set ws1 = Worksheets("データーベース")

    ws.Select
    Cells.Select
    Selection.ClearContents
    ws1.Select
    Cells.Select
    Selection.Copy
    ws.Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False





'===============宣言1===============
Dim maxrow As Long
maxrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long
Dim k As Long
Dim j As Long
i = 2
k = 2
j = 2
' Do Until Cells(k, 1).Value = ""
' Cells(k, 5).Value = Cells(k, 1).Value & Cells(k, 4).Value
' k = k + 1

' Loop

'===============並び替え===============

Range("A2:O" & maxrow).Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("C2"), Order2:=xlAscending
Range("A2:O" & maxrow).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("L2"), Order2:=xlAscending
Range("A2:O" & maxrow).Sort Key1:=Range("L2"), Order1:=xlAscending, Key2:=Range("J2"), Order2:=xlAscending



'===============条件処理===============
'メモ　C=2=生産年月,6=F=EDNO, 8=H=SPEC, 10=J=生産工場コード, 12=L=作業者


Do Until Cells(i, 1).Value = ""

'1
If Cells(i, 6).Value <> Cells(i + 1, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value
'2
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 10).Value = Cells(i + 1, 10).Value And _
       Cells(i, 6).Value <> Cells(i + 2, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value
'3
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 10).Value = Cells(i + 1, 10).Value And _
       Cells(i, 6).Value <> Cells(i + 3, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value
'4
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 4, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value
'5
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 5, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value
'6
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 6, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value
'7
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 7, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value
'8
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 8, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value
'9
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 9, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8)
'10
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 10, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value
'11
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 11, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value

'12
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 12, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value
'13
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 13, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8)
'14
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
        Cells(i, 6).Value <> Cells(i + 14, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value
'15
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 15, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value

'16
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value = Cells(i + 15, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 16, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value & Cells(i + 15, 8).Value

'17
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value = Cells(i + 15, 6).Value And _
       Cells(i, 6).Value = Cells(i + 16, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 17, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value & Cells(i + 15, 8).Value & _
                    Cells(i + 16, 8)
'18
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value = Cells(i + 15, 6).Value And _
       Cells(i, 6).Value = Cells(i + 16, 6).Value And _
       Cells(i, 6).Value = Cells(i + 17, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 18, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value & Cells(i + 15, 8).Value & _
                    Cells(i + 16, 8) & Cells(i + 17, 8)
'19
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value = Cells(i + 15, 6).Value And _
       Cells(i, 6).Value = Cells(i + 16, 6).Value And _
       Cells(i, 6).Value = Cells(i + 17, 6).Value And _
       Cells(i, 6).Value = Cells(i + 18, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 19, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value & Cells(i + 15, 8).Value & _
                    Cells(i + 16, 8) & Cells(i + 17, 8) & Cells(i + 18, 8)
'20
ElseIf Cells(i, 6).Value = Cells(i + 1, 6).Value And _
       Cells(i, 6).Value = Cells(i + 2, 6).Value And _
       Cells(i, 6).Value = Cells(i + 3, 6).Value And _
       Cells(i, 6).Value = Cells(i + 4, 6).Value And _
       Cells(i, 6).Value = Cells(i + 5, 6).Value And _
       Cells(i, 6).Value = Cells(i + 6, 6).Value And _
       Cells(i, 6).Value = Cells(i + 7, 6).Value And _
       Cells(i, 6).Value = Cells(i + 8, 6).Value And _
       Cells(i, 6).Value = Cells(i + 9, 6).Value And _
       Cells(i, 6).Value = Cells(i + 10, 6).Value And _
       Cells(i, 6).Value = Cells(i + 11, 6).Value And _
       Cells(i, 6).Value = Cells(i + 12, 6).Value And _
       Cells(i, 6).Value = Cells(i + 13, 6).Value And _
       Cells(i, 6).Value = Cells(i + 14, 6).Value And _
       Cells(i, 6).Value = Cells(i + 15, 6).Value And _
       Cells(i, 6).Value = Cells(i + 16, 6).Value And _
       Cells(i, 6).Value = Cells(i + 17, 6).Value And _
       Cells(i, 6).Value = Cells(i + 18, 6).Value And _
       Cells(i, 6).Value = Cells(i + 19, 6).Value And _
       Cells(i, 6).Value <> Cells(i + 20, 6).Value Then
Cells(i, 8).Value = Cells(i, 8).Value & Cells(i + 1, 8).Value & Cells(i + 2, 8).Value & Cells(i + 3, 8).Value & _
                    Cells(i + 4, 8).Value & Cells(i + 5, 8).Value & Cells(i + 6, 8).Value & Cells(i + 7, 8).Value & _
                    Cells(i + 8, 8) & Cells(i + 9, 8).Value & Cells(i + 10, 8).Value & Cells(i + 11, 8).Value & _
                    Cells(i + 12, 8) & Cells(i + 13, 8).Value & Cells(i + 14, 8).Value & Cells(i + 15, 8).Value & _
                    Cells(i + 16, 8) & Cells(i + 17, 8) & Cells(i + 18, 8) & Cells(i + 19, 8)



'SPECのデーターの個数が増えたらここに条件追加


End If


i = i + 1

Loop

Cells(1, 16).Value = "検査1"
Cells(1, 17).Value = "検査2"
Cells(1, 18).Value = "検査3"
Cells(1, 19).Value = "検査4"
Cells(1, 20).Value = "検査5"
Cells(1, 21).Value = "検査6"
Cells(1, 22).Value = "検査7"
Cells(1, 23).Value = "検査8"
Cells(1, 24).Value = "検査9"
Cells(1, 25).Value = "検査10"
Cells(1, 26).Value = "検査11"
Cells(1, 27).Value = "検査12"
Cells(1, 28).Value = "検査13"
Cells(1, 29).Value = "検査14"
Cells(1, 30).Value = "検査15"
Cells(1, 31).Value = "検査16"
Cells(1, 32).Value = "検査17"
Cells(1, 33).Value = "検査18"
Cells(1, 34).Value = "移動"

Do Until Cells(k, 1).Value = ""
Cells(k, 35).Value = Cells(k, 6).Value & Cells(k, 12).Value & Cells(k, 10).Value



k = k + 1

Loop


'===============重複の削除===============

 ActiveSheet.Range("$A$1:$AI$" & maxrow).RemoveDuplicates Columns:=35, Header:=xlYes
Range("AI:AI") = ""
'Do Until Cells(j, 1).Value = ""


'If Cells(j, 1).Value <> Cells(j + 1, 1).Value Then
'Cells(j, 3).Value = Cells(j, 3).Value

'ElseIf Cells(j, 1).Value = Cells(j + 1, 1).Value And _
'       Cells(j, 1).Value <> Cells(j + 2, 1).Value Then
'Cells(j, 3).Value = Cells(j, 3).Value + Cells(j + 1, 3).Value
'End If


'j = j + 1

'Loop


'===============重複の削除===============

' ActiveSheet.Range("$A$1:$E$" & maxrow).RemoveDuplicates Columns:=1, Header:=xlYes

ActiveWorkbook.Save

End Sub
0 likes

3Answer

hiro_underclass
@hiro_underclass

セルを指定するときはシートを、シートを指定するときはブックを指定することが望ましいと思います。
指定しないと、「セル・・・どのシートの？」「シート・・・どのブックの？」といったように迷子になってしまいます。

＊＊＊(追記)＊＊＊
もう少し正確に説明しますと、ブックを指定せずにシートを指定するとアクティブブックのシートが、シートを指定せずにセルを指定するとアクティブシートのセルが参照されます。
したがって、他のブックを開いている状態でSet ws = Worksheets("元")を実行すると、他のブックから「元」シートを探そうとすることになります。そのようなシートがないためエラーになったと思われます。
＊＊＊＊＊＊＊＊＊

修正前

Dim wb As Workbook
Dim ws, ws1 As Worksheet
Set wb = ThisWorkbook

Set ws = Worksheets("元")
Set ws1 = Worksheets("データーベース")

    ws.Select
    Cells.Select
    Selection.ClearContents
    ws1.Select
    Cells.Select
    Selection.Copy
    ws.Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

修正後

Dim wb As Workbook
Dim ws As Worksheet,  ws1 As Worksheet
Set wb = ThisWorkbook

Set ws = wb.Worksheets("元")
Set ws1 = wb.Worksheets("データーベース")

ws.Cells.ClearContents
ws1.Cells.Copy
ws.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
1Like

Comments

  1. @frswataru

    Questioner

    ユーザー関数をつかうと。
    しっかり最後までオブジェクトを指定すれば良いということですね。
radames1000
@radames1000

ブック指定をしてみてはいかがでしょう。

Dim wb As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Set wb = ThisWorkbook

Set ws = wb.Worksheets("元")
Set ws1 = wb.Worksheets("データーベース")
0Like

Comments

  1. @frswataru

    Questioner

    状況変わらずでした

  2. @radames1000

    ではシート名が正しいか確認してください。
    スペースが入っていることがよくあります。

Your answer might help someone💌

LoginSign Up