[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『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 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
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
[結果]シート |[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
またシートは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
ワークシートをいちいち選択せず、8月が知りたい場合は「8」を指定するまで
繰り返す、という考えで合ってますでしょうか。
VBAはじめたばかりで素人すぎて大変申し訳ありません。
よろしくお願いいたします。
(franny) 2021/11/24(水) 15:58
ピンクさんのコードを借用すると
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
>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
それぞれご丁寧にありがとうございます。
Worksheets(1)とWorksheets(”1”)の違いをきちんと理解していなかったので
まずそこから調べます。
きまぐれおじさん様とピンク様
両方試してみました、集計されますが結果シートのA列の商品名の横全てにsheet1と入力しないと
数量が自動計算されないのでここも最終行を取得して自動で集計されるようにしたいです。
自分でも考えてみます。
重ね重ねありがとうございます。
とても勉強になっております。
(franny) 2021/11/25(木) 10:30
>Worksheets(1)は左から1番目のシート Worksheets(”1”)は1という名前のシート、という認識で合ってますでしょうか。 その通りです。 (,,ゆ ゅ) 2021/11/25(木) 11:23
既に答えが付いてますが合ってます。
なお↓で解説済みです。(読んでなかったのですね・・・・)
[[20211118144119]] 『セルの値と同じ名前のシートをアクティブにする』(franny)
(もこな2) 2021/11/25(木) 12:17
(γ) 2021/11/25(木) 13:26
ご回答いただいていることには大変感謝しております。
ありがとうございます。
(franny) 2021/11/25(木) 15:35
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.