Excel VABについて 1.xlsm、VBA.xlsm２つのファイルがあり、1.xlsmにてVB

Excel　VABについて
1.xlsm、VBA.xlsm２つのファイルがあり、1.xlsmにてVBAを実行します。

①VBA.xlsm　ファイルを開く

繰り返し作業
②find関数を使って検索ヒットしたセルに合計数量を入力
※検索値が”-”の時は何もしない
③完了したら〇をIP

②からが上手く実行できません。
VBA初心者な為、教えて頂けると助かります。
宜しくお願い致します。

Sub 合計()

Dim Startrow As Long, Lastrow As Long, col_num As Long, rowkey1 As Long, rowkey2 As Long, i As Long, aSum As Long, bSum As Long
Startrow = Workbooks("VBA.xlsm").Worksheets("明細").Cells(Rows.Count, 14).End(xlUp).Row + 1
Lastrow = Workbooks("VBA.xlsm").Worksheets("明細").Cells(Rows.Count, 4).End(xlUp).Row


Workbooks.Open Filename:="\\xxxxx\xxxxx\1.xlsm", writerespassword:="0000"
Workbooks("VBA.xlsm").Worksheets("明細").Activate

For i = Startrow To Lastrow
col_num = Workbooks("1.xlsm").Worksheets("合計").Rows(6).Find(What:=Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 4), LookAt:=xlWhole).Column + 3
rowkey1 = Workbooks("1.xlsm").Worksheets("合計").Columns(6).Find(What:=Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 6), LookAt:=xlWhole).Row
rowkey2 = Workbooks("1.xlsm").Worksheets("合計").Columns(6).Find(What:=Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 9), LookAt:=xlWhole).Row
aSum = Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 8) + Workbooks("1.xlsm").Worksheets("合計").Cells(rowkey1, col_num)
bSum = Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 11) + Workbooks("1.xlsm").Worksheets("合計").Cells(rowkey2, col_num)


If Cells(i, 6) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Activate
Cells(rowkey1, col_num) = aSum
End If

If Cells(i, 9) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Activate
Cells(rowkey2, col_num) = bSum
End If

Workbooks("VBA.xlsm").Worksheets("明細").Activate
Cells(i, 14) = "〇"
ActiveSheet.Calculate

Next i

End Sub

質問者からの補足コメント

• 説明不足で申し訳ありません。表のサンプルです。
  上段VBA.xlsm 明細シート
  下段1.xlsm 合計シート
  
  明細シート（上段）にて稼働欄が"◯"になっていない
  行から作業を行います。
  合計シート（下段）にて商品名と店舗が一致する修正欄に明細の数量を追加して行きます。
  追加が完了したら明細シート（上段）にて稼働欄に"◯"をIPします。
  
  VBA実行したところ
  
  col_num = Workbooks("1.xlsm").Worksheets("合計").Rows(6).Find(What:=Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 4), LookAt:=xlWhole).Column + 3
  
  上記コードにて、
  オブジェクト変数またはwithブロック変数が設定されていません
  ーとエラーになりました。
  
  宜しくお願い致します

  
    補足日時：2021/12/13 22:13

No.6 ベストアンサー 回答者： Qchan1962 回答日時： 2021/12/14 00:31

連投すみません　訂正します。

多分。
If Not col_Key Is Nothing And Not rowkey1 Is Nothing And Not rowkey2 Is Nothing Then
これは、行き過ぎでした（処理的に間違いですね）
ここの部分は個別に条件を設定するべきかも

If Not col_Key Is Nothing And Not rowkey1 Is Nothing And Not rowkey2 Is Nothing Then　以下を

If Not col_Key Is Nothing Then
col_num = col_Key.Column + 3
If Not rowkey1 Is Nothing And .Cells(i, 6) <> "-" Then
r1 = rowkey1.Row
sumSh.Cells(r1, col_num).Value = .Cells(i, 8).Value + sumSh.Cells(r1, col_num).Value
End If
If Not rowkey2 Is Nothing And .Cells(i, 9) <> "-" Then
r2 = rowkey2.Row
sumSh.Cells(r2, col_num).Value = .Cells(i, 11).Value + sumSh.Cells(r2, col_num).Value
End If
.Cells(i, 14).Value = "〇"
End If
End If

Next i　までの間部分
ひょっとすると　If .Cells(i, "N").Value <> "〇" Then　もいらないかも

この回答へのお礼

お返事ありがとうございます。
無事VBA実行できました。
コードも綺麗にして頂いてわかりやすくなりました。ありがとうございます。

お礼日時：2021/12/14 12:52

No.5 回答者： Qchan1962 回答日時： 2021/12/14 00:16

＃２です

＞『Nextに対応するForがありません』と表示されました。

書き直された位置の問題かと思いますが、、試していないので解りません。

＃３様が回答されている通り、Findで見かからない場合の対策は
現状必ずあるとしても必須です。is Nothing で対策しないにしても何だかのエラー対処をするべきと思います。

表組みが良く見えず解らないのですが、ご質問コードを見ながら
サンプルを書いてみましたが、デバッグ環境を作る事が出来ないので
試していません。

変数もわざわざ設定しなくて良いかも知れませんが、一応
型をわかり易くするために設定してみました。
参考になれば良いのですが、ダメかもしれません。。。

Sub sample()
Dim Startrow As Long, Lastrow As Long
Dim i As Long, col_num As Long
Dim r1 As Long, r2 As Long
Dim rowkey1 As Range, rowkey2 As Range, col_Key As Range
Dim BK1 As Workbook
Dim sumSh As Worksheet

Set BK1 = Workbooks.Open(Filename:="\\xxxxx\xxxxx\1.xlsm", writerespassword:="0000")
Set sumSh = BK1.Worksheets("合計")

With Workbooks("VBA.xlsm").Worksheets("明細")
Startrow = .Cells(Rows.Count, 14).End(xlUp).Row + 1
Lastrow = .Cells(Rows.Count, 4).End(xlUp).Row
For i = Startrow To Lastrow
If .Cells(i, "N").Value <> "〇" Then
Set col_Key = sumSh.Rows(6).Find(What:=.Cells(i, 4), LookAt:=xlWhole)
Set rowkey1 = sumSh.Columns(6).Find(What:=.Cells(i, 6), LookAt:=xlWhole)
Set rowkey2 = sumSh.Columns(6).Find(What:=.Cells(i, 9), LookAt:=xlWhole)
If Not col_Key Is Nothing And Not rowkey1 Is Nothing And Not rowkey2 Is Nothing Then
col_num = col_Key.Column + 3
r1 = rowkey1.Row
r2 = rowkey2.Row
If .Cells(i, 6) <> "-" Then
sumSh.Cells(r1, col_num).Value = .Cells(i, 8).Value + sumSh.Cells(r1, col_num).Value
End If
If .Cells(i, 9) <> "-" Then
sumSh.Cells(r2, col_num).Value = .Cells(i, 11).Value + sumSh.Cells(r2, col_num).Value
End If
.Cells(i, 14).Value = "〇"
End If
End If
Next i
End With

End Sub

No.4 回答者： fujillin 回答日時： 2021/12/13 23:49

No3です。

＞上記コードにて、
＞オブジェクト変数またはwithブロック変数が設定されていません
＞ーとエラーになりました。
No3に記したように、FindメソッドでNothingが返されたとき（＝該当するセルが存在しない）に、その列番号を取得しようとしてエラーになっているものと考えられます。

エラーを回避するには、一旦、戻り値を変数で受けて判断をなされば宜しいかと。
例えば
Set tmp = Workbooks("1.xlsm").Worksheets("合計").Rows(6).Find(What:=Workbooks("VBA.xlsm").Worksheets("明細").Cells(i, 4), LookAt:=xlWhole)

If tmp Is Nothing Then
　' 検索値が見つからない場合の処理
Else
　col_num = tmp.Column + 3
End If
など。

他の方法としては、エラー処理を加えるという方法もあります。
　On Error Goto Label
あるいは
　On Error Resume Next
など。
https://docs.microsoft.com/ja-jp/office/vba/lang …


ついでながら・・
最初にシートを変数に設定しておけば、以降は記述を簡略化できます。
例えば、
　Set sGoukei = Workbooks("1.xlsm").Worksheets("合計")
　Set sMeisai = Workbooks("VBA.xlsm").Worksheets("明細")
を最初に設定しておけば、以降は（例えば上記の1行目）以下のように記述できます。
Set tmp = sGoukei.Rows(6).Find(What:=sMeisai.Cells(i, 4), LookAt:=xlWhole)

No.3 回答者： fujillin 回答日時： 2021/12/13 22:01

こんばんは

内容がよくわかりませんけれど・・
すでに回答が出ているので、出ていない点を一つ。

FINDメソッドは検索対象が見つからない場合にNothingを返します。
直接、.Column や .Row を取得しようとしていますが、見つからない場合はエラーが発生します。
詳細が不明ですが、もしも「上手くいかない」というのがエラーだったりするのであれば、そのあたりも確認なさってみてはいかがでしょうか？


ご質問には関係ありませんけれど、記述法に関して・・・
・同じブックやシートが頻出するような場合は、シートを変数に代入しておくか、既出のようにWIth構文を利用することで、記述を簡潔にすることができます。
（また、若干ながらですが処理速度も向上するはずです）
・シートのActivateは実行時の画面のチラつきの原因になりやすいです。
VBAの場合は、シートオブジェクトを付せばアクティブでないシートにも直接アクセスが可能ですので、そのような記述をなさった方が宜しそうに思います。

この回答へのお礼

内容がわかりづらくて申し訳ありません。補足にて表サンプル追加したので宜しくお願い致します。
FINDメゾットについて、検索対象が見つからないのは問題なので、あえてNothingは省きました。

御返答ありがとうございます。わかりづらくて申し訳ありません。補足にて、表サンプルをアップロードしたので、宜しくお願い致します。FIND関数については、検索値は検索対象に必ずある為、Nothingは省きました。

WithやActivateの記述法アドバイスありがとうございます。修正致します。

お礼日時：2021/12/13 23:28

No.2 回答者： Qchan1962 回答日時： 2021/12/13 18:28

こんばんは、

Find　については、取敢えず置いといて
気になるのは、
If Cells(i, 6) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Activate

If Cells(i, 9) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Activate

このCells(i, 6)は
ループの上記で実行している
Workbooks("VBA.xlsm").Worksheets("明細").Activate　ですが

If Cells(i, 9) <> "-" Then　は
Workbooks("VBA.xlsm").Worksheets("明細")　ではなく
Workbooks("1.xlsm").Worksheets("合計")　かも知れませんね

このような処理の場合、Activateを使わず、出来るだけ纏めて行うようにした方が良いように思います

この部分の一例
With Workbooks("VBA.xlsm").Worksheets("明細")
If .Cells(i, 6) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Cells(rowkey1, col_num) = aSum
End If
If .Cells(i, 9) <> "-" Then
Workbooks("1.xlsm").Worksheets("合計").Cells(rowkey2, col_num) = bSum
End If
.Cells(i, 14) = "〇"
.Calculate
End With

確かめていないので違っていたらごめんなさい。。

この回答へのお礼

御返答ありがとうございます。
一例に修正したところ、
『Nextに対応するForがありません』と表示されました。

詳細不足で申し訳ありません。
補足にて表サンプルを追加したので、宜しくお願い致します。

お礼日時：2021/12/13 22:19

No.1 回答者： めぐみん_ 回答日時： 2021/12/13 18:05

・どのようなデータ（シートの状態）になっているのか？

・上手くいかないとは具体的にどんな事？（コードで推測してって？）

取り敢えずFindメソッドは開始セルを指定しないと、直前のセルの次から探すと思いました。（記憶違いでなければ）
なので本来探すべきセルが上にあるのに、違うセルを見つけてしまっていると言うのなら考えられそうですけど。

この回答へのお礼

申し訳ありません。補足にて表サンプル追加しました。御確認頂けると幸いです。

お礼日時：2021/12/13 22:19