エクセルVBA VLOOKUPを使ってのカウント作業

VBA内でVLOOKUPを使い、条件に当てはまる人数をカウントしたいと
考えています。

シート３に
氏名　　所属　　担当1　　担当2　　担当3 …

太郎　　１　　　　１　　　　１　　　
次郎　　２　　　　　　　　　１
三郎　　１　　　　　　　　　１
四郎　　２　　　　１

がデータとして入力されています。

シート１に

氏名
太郎
四郎


担当１合計　ｘ
担当２合計　ｙ

としてあります。

やりたいことは、シート３のデータを使いVBA上で計算をして担当数の合計を出すことを
考えています。
知識が浅はかですので、VLOOKUPを使っての作成ですが、

担当2について

Dim k As Long
Dim j As Long

j = 0

For k = 5 To 24

If WorksheetFunction.VLookup(Worksheets(1).Cells(k, 3), Worksheets(3).Range(Cells(1, 1), Cells(100, 6)), 4, False) = 1 Then

j = j + 1

next k

End If

として動かすと、構文が間違っているようで、エラー1004と表示されてしまいます。
構文の間違いを教えていただけますでしょうか。

不備がありましたらご指摘ください。

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

• 回答ありがとうございます。
  
  ご指摘いただいた通り、 ”A1:F100”の範囲指定で正しく動きました。
  
  ここはrange(cells…）ではなく、通常の ”A1:F100”のような指定がよいのでしょうか？
  cellsの範囲指定はできるものなのでしょうか？？

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/02/19 10:22

No.2 ベストアンサー 回答者： fujillin 回答日時： 2023/02/19 10:29

こんにちは

＞エラー1004と表示されてしまいます。
エラー1004は「実行時エラー」のはずなので、「構文エラー」ではありません。
一方で、ご提示のコードには構文のエラーがありますので、先にそちらでひっかかるはずですが・・？

まずは、そちらに関して・・
「 For ～～ Next 」
と
「 IF ～～ End If 」
はどちらかにもう一方が包含されている関係である必要があります。
しかし、ご提示のコードでは、互いにまたがってしまう関係になっています。
それなので、エクセルがどう解釈したらよいのかわからないと言ってくるはずです。

ついでながら、実行時エラーになりそうなのは、
＞Worksheets(3).Range(Cells(1, 1), Cells(100, 6))
例えば、 Cells(1, 1) 等は、アクティブシートのA1セルと見做されますので、実行時にアクティブなシートが「Worksheets(3)」以外の場合にはエラーになります。
逆に言えば、Worksheets(3)をアクティブの状態にして実行すれば、エラーにはなりません。


また、ご質問には関係ありませんけれど、列の合計を出したいだけのように思われますが、関数を使って求めるのなら、SUM関数を利用すれば済む話のように思われますが・・？

この回答へのお礼

回答ありがとうございました。
ヒントをいただき、range(worksheets(3).cells(…　ということに気づきました。

お礼日時：2023/02/19 10:55

No.1 回答者： 銀鱗 回答日時： 2023/02/19 09:54

Worksheets(3).Range(Cells(1, 1), Cells(100, 6))

この指定方法が間違っている。
　Sheet3の
　A1セルには何が入力されていますか。
　F100セルには何が入力されていますか。
そこにセルの範囲を指定するための値が入力されていれば、正しく動作しますよ。
たぶん ”A1:F100” のように指定したいところが、
シート3のA1セルの値からF100セルの値で指定される範囲……という訳の分からない指定になっているのでしょう。

この回答へのお礼

回答ありがとうございました。

お礼日時：2023/02/19 10:53