[[20211122103432]] 『VBAでVLOOLUP』(franny) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『VBAでVLOOLUP』(franny)

いつもお世話になっております。

”結果”シートに

A         B      C
1シートの名前特定 品名    数量
2         オレンジ  ()
3          リンゴ
4 バナナ  

A1で指定したシート(シートは1〜12まで)
A      B
品名    数量
オレンジ  2
バナナ   3
りんご   4
メロン   5
とあり、”結果”シートのC列に数量を反映させたいVBAを書きますが
変数エラーが出ます。
書いたVBAは
Sub sample1() 'セルに入力した値のシートをアクティブにする。

    Dim i As String
        i = ActiveSheet.Range("F1").Value
        Worksheets(i).Activate
End Sub

Sub sample2() 'VLOOKUPで結果シートに集計

    Dim j As String
    With Sheets("結果")
        For j = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(j, 3) = WorksheetFunction.VLookup( _
                .Cells(j, 2), Sheets(i).Range("B:B"), 3, 0)
    Next
    End With
End Sub

になります。
どこが間違えなのか特定できないでいます。
お知恵をお借りできれば幸いです。
よろしくお願いいたします。

< 使用 Excel:Office365、使用 OS:Windows10 >


 >どこが間違えなのか特定できないでいます。

 っていうか、普通の数式も書けてないと思います。
 検査値,範囲,列番号,検索方法
        ^^^^
 範囲に対する列番号。
(にくちゃんねる) 2021/11/22(月) 10:59

すみません、質問用に変える部分忘れてました
 Dim j As String

    With Sheets("結果")
        For j = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(j, 3) = WorksheetFunction.VLookup( _
                .Cells(j, 2), Sheets(i).Range("A:A"), 2, 0)
    Next
    End With
End Sub
(franny) 2021/11/22(月) 11:06

  Dim j As String←文字列型です
カウンタ変数なので整数型にして下さい
Sheets(i)←iには何も代入されていません
WorksheetFunction.VLookup
ループさせてを使用するより範囲にVLOOKUP関数を
一括でいれて
Range.Value=Range.Value
とした方がよいと思います
(どん) 2021/11/22(月) 11:13

 再度

 っていうか、普通の数式も書けてないと思います。
 検査値,範囲,列番号,検索方法
        ^^^^
 範囲に対する列番号。
 いい加減な質問をしないでほしいと言うか、
 変数の中身も含めそれぐらいは自分で解決してください。
(にくちゃんねる) 2021/11/22(月) 11:38

ありがとうございます。
Dim j as Longですね。
iはその前の
Sub sample1()
 Dim i As String
        i = ActiveSheet.Range("F1").Value
        Worksheets(i).Activate
End Sub
でセルの値と同じ名前のシートをアクティブにするとしたので使ったのですが
使い方間違えてますでしょうか。

範囲にVLOOKUP関数を使う、参考に致します。

まだVBAやり始めなので拙いことが多いですが
きちんと回答いただき感謝しております。
ありがとうございます。
(franny) 2021/11/22(月) 12:02


 >アクティブにするとしたので使ったのですが
使い方間違えてますでしょうか。
アクティブにしたのなら
Sheets(i).Range("A:A")
の
Sheets(i)は不要ですが
Sub sample1()
i = ActiveSheet.Range("F1").Value
のiは適用されません、変数の適用範囲(スコープ)を調べて下さい
(にくちゃんねる)さんの指摘は、範囲が1列(A:A)なのに
列番号が2になっている・・です
(どん) 2021/11/22(月) 12:23

>セルの値と同じ名前のシートをアクティブにするとしたので使ったのですが
>使い方間違えてますでしょうか。
間違えてはいないと思います。

ただ、慣例として i〜nまでの一文字(*)は、
数値のカウンター変数として使うことが多いので、
変数 s などを使うと文字列であろうと目視で推測もでき、
紛れることがないと思います。

もちろん、Worksheets(2) とか、左から何番目のワークシートかという指定方法もとれるわけです。

(*)integerと関連づけて、iからnまでがよく使われるようです。
(γ) 2021/11/22(月) 12:27


どん様
ありがとうございます。
同じモジュール内でしか変数が使えないのならば、
セルの値と同じ名前のシートの数量をVLOOKUPで引っ張ってくる、という作業を一気に行いたいならば
Sub sample1とSub sample2()に分けないで
Sub sample1() 'セルに入力した値のシートをアクティブにしVLOOKUPで数量を引っ張ってくる。
    Dim i As String
  Dim j As Long 
    With Sheets("結果")

        i = ActiveSheet.Range("F1").Value
        Worksheets(i).Activate

        For j = 2 To .Cells(.Rows3.Count, 1).End(xlUp).Row
            .Cells(j, 3) = WorksheetFunction.VLookup( _
                .Cells(j, 2),.Range("A$:B$"), 2, 0)
    Next
    End With
End Sub
なのかなと思いましたがRangeメソッドは失敗しました
になります。A:Aは失礼しました。A$:B$ですね、申し訳ありません。

Y様
変数の設定の仕方にも慣例があるのですね
まだiを使用する程度にしか理解してませんでした。
そしてiを使ってしまったので単純にjと考えてしまいました。
数値ならi、ならセルの値と同じ名前のシートをアクティブにする、のほうの変数をs
にしてVloolupのほうをiにするのが自然という理解で合ってますでしょうか。

お二人ともご丁寧にありがとうございます。
初心者過ぎて呆れることもあると思いますが、よろしくお願いいたします。
(franny) 2021/11/22(月) 13:57


 >A$:B$ですね、申し訳ありません。

 だから、数式でそれが通じるどうか少しは試したらどうですか。

 =sum(A:B)
 とセルに書いて、編集状態でカーソルがA:B辺りにある時にF4キーを押す。
 っていうか、マクロなので、その文字は変わらないので絶対参照で無くても良いです。
(にくちゃんねる) 2021/11/22(月) 14:12

にくちゃんねる様

絶対参照でなくてよいのですね、どうもありがとうございます。
Rangeメソッドは失敗しましたから「アプリケーション定義またはオブジェクトの定義エラーです」
に変化したので、そもそも「アプリケーション定義または〜」のエラーについて調べているところです。
触りながら覚えようとしているので素人すぎて苛立たしい部分も多いと思います。
申し訳ございません。
お知恵を貸していただければ幸いです。ありがとうございます。
(franny) 2021/11/22(月) 14:27


 Rows.CountがRows3.Countに変わっていますよ
(どん) 2021/11/22(月) 14:37

どん様
すみません、タイプミスです。
(franny) 2021/11/22(月) 14:42

 [結果]シート
    |[A]         |[B]     |[C] 		
 [1]|シートの名前|品名    |数量		
 [2]|Sheet1      |オレンジ|    		
 [3]|Sheet2      |リンゴ  |    		
 [4]|Sheet3      |バナナ  |    		

 Sheet1〜Sheetxx
    |[A]     |[B] 		
 [1]|品名    |数量		
 [2]|オレンジ| 2		
 [3]|バナナ  | 3		
 [4]|リンゴ  | 4		
 [5]|メロン  | 5		

 上記の様なレイアウトで

 Sub Test1()
    Dim i As Long
    With Worksheets("結果")
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(i, 3).Value = WorksheetFunction.VLookup(.Cells(i, 2).Value, _
                    Worksheets(.Cells(i, 1).Value).Range("A:B"), 2, False)
        Next
    End With
 End Sub

(ピンク) 2021/11/22(月) 16:48


ピンク様
分かりやすくまとめていただきありがとうございます。
WorksheetFunctionクラスのVlookUpプロパティを取得できません、
になってしまいます。

またシートは1〜12まで連番で振っていて、これは月を表していて、
適宜1月のシートのVLOOKUPの集計を結果シートに反映、3月が見たい時には3を指定
というようにしたく、
Dim i As String

        i = ActiveSheet.Range("F1").Value
        Worksheets(i).Activate
といれたのですが、これはそもそも無駄でしょうか。

質問ばかりで申し訳ありません、ご教示のほどよろしくお願いいたします。
(franny) 2021/11/24(水) 14:03


横から失礼します。

>WorksheetFunctionクラスのVlookUpプロパティを取得できません、
>になってしまいます。
それは対応するキーが検索範囲に無い場合に出されるメッセージです。
ワークシート上で同じことを数式でやってみて、結果が返ってくるか確認してください。

>これはそもそも無駄でしょうか。
私の回答に誤解があってはいけないので、追加説明します。
ワークシートの指定自体は正しいです。

しかし、
ワークシートを逐一選択状態にせずに、
直接、ワークシートを参照して操作するほうが間違いも少なく、
分かりやすいので、選択しない方法が推奨されるのです。

理由を詳しく書くと、以下のとおりです。
・数行のコードであればいいですが、長くなると、
 どのシートが今アクティブかということを常に意識することは
 ユーザー(将来の自分を含む)にとって負荷となりますし、
・シートやセルを選択することで速度低下の原因ともなりえます(特に広い範囲を対象とした場合)も
 ありえます。
(γ) 2021/11/24(水) 14:27


ご回答ありがとうございます。
数式だと通るのですが、VBAにするとどうしてもエラーが出ます。
上記表と同じ状態を作成しているのですが……。

ワークシートをいちいち選択せず、8月が知りたい場合は「8」を指定するまで
繰り返す、という考えで合ってますでしょうか。

VBAはじめたばかりで素人すぎて大変申し訳ありません。

よろしくお願いいたします。
(franny) 2021/11/24(水) 15:58


ありがとうございます。
できました。
が、Sheet1のみの計算結果しか出ないです。
他のSheet(他の月)の数量を引っ張ってきたい時は
どうすればよいでしょうか。
重ね重ね失礼いたします。
(franny) 2021/11/24(水) 16:04

Worksheets(.Cells(i, 1).Value).Nameを調べてみてはいかがでしょうか。
セルの値が数字の場合、シートの名前ではなくインデックスになっているのではないかと思います。

ピンクさんのコードを借用すると

 Sub Test1()
    Dim i As Long
    With Worksheets("結果")
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            Debug.Print Worksheets(.Cells(i, 1).Value).Name '←イミディエイトウィンドウを確認してください。
            .Cells(i, 3).Value = WorksheetFunction.VLookup(.Cells(i, 2).Value, _
                    Worksheets(.Cells(i, 1).Value).Range("A:B"), 2, False)
        Next
    End With
 End Sub

このような感じです。
(きまぐれおじさん) 2021/11/24(水) 16:10


ありがとうございます。
調べてみます。Debug.Printというのも初見なのでまずはググって調べるところから始めます。
ご丁寧にありがとうございます。
(franny) 2021/11/24(水) 16:20

シート名としてセルにある情報を文字列変数に入れてから使えば
大丈夫なはずです。
Worksheets(2)と
Worksheets(“2”)との違いをよく理解して下さい。
うまくダブルクォテーションが入力できません。忖度下さい。
(γ) 2021/11/24(水) 17:12

 >3月が見たい時には3を指定というようにしたく、
 了解です

 [結果]シート
    |  [A]       |  [B]   | [C] 	
 [1]|シートの名前|品名    |数量	
 [2]|           1|オレンジ|    	
 [3]|           2|リンゴ  | 
 [4]|           3|バナナ  | 

 ですね

 Sub Test1()
    Dim i As Long
    With Worksheets("結果")
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(i, 3).Value = WorksheetFunction.VLookup(.Cells(i, 2).Value, _
                    Worksheets(CStr(.Cells(i, 1).Value)).Range("A:B"), 2, False)
        Next
    End With
 End Sub

 変更箇所
 Worksheets(.Cells(i, 1).Value)
        ↓
 Worksheets(CStr(.Cells(i, 1).Value))

(ピンク) 2021/11/24(水) 18:44


 >WorksheetFunctionクラスのVlookUpプロパティを取得できません、
 見つからなかった時のエラー対策

 Sub Test2()
    Dim i As Long

    On Error Resume Next
    With Worksheets("結果")
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(i, 3).Value = WorksheetFunction.VLookup(.Cells(i, 2).Value, _
                    Worksheets(CStr(.Cells(i, 1).Value)).Range("A:B"), 2, False)
            If Err Then Cells(i, 3).Value = "該当なし"
            Err.Clear
        Next
    End With
    On Error GoTo 0
 End Sub

(ピンク) 2021/11/24(水) 18:56


きまぐれおじさん様
Y様
ピンク様

それぞれご丁寧にありがとうございます。
Worksheets(1)とWorksheets(”1”)の違いをきちんと理解していなかったので
まずそこから調べます。

きまぐれおじさん様とピンク様
両方試してみました、集計されますが結果シートのA列の商品名の横全てにsheet1と入力しないと
数量が自動計算されないのでここも最終行を取得して自動で集計されるようにしたいです。

自分でも考えてみます。

重ね重ねありがとうございます。
とても勉強になっております。

(franny) 2021/11/25(木) 10:30


Worksheets(1)は左から1番目のシート
Worksheets(”1”)は1という名前のシート、という認識で合ってますでしょうか。
(franny) 2021/11/25(木) 10:32

 >Worksheets(1)は左から1番目のシート
  Worksheets(”1”)は1という名前のシート、という認識で合ってますでしょうか。
 その通りです。
(,,ゆ ゅ) 2021/11/25(木) 11:23

>Worksheets(1)は左から1番目のシート
>Worksheets(”1”)は1という名前のシート、という認識で合ってますでしょうか。

既に答えが付いてますが合ってます。
なお↓で解説済みです。(読んでなかったのですね・・・・)
[[20211118144119]] 『セルの値と同じ名前のシートをアクティブにする』(franny)

(もこな2) 2021/11/25(木) 12:17


ありゃあ、その件だけを扱った質疑のスレッドがあったんですか?
しかも一週間前じゃないですか。これには唖然。
頑張ってくださいねえ。

(γ) 2021/11/25(木) 13:26


まだ覚えたてなので大変拙いこと承知しております。
3冊本は読みましたが、実際やりたいことを考えて書くのにはまだ未熟で苦労を擁しております。

ご回答いただいていることには大変感謝しております。
ありがとうございます。
(franny) 2021/11/25(木) 15:35


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.