Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3738 / 13646 ツリー ←次へ | 前へ→

【60288】2つの条件でのVLOOKUPをマクロで・・・ モー 09/2/7(土) 19:59 質問[未読]
【60289】Re:2つの条件でのVLOOKUPをマクロで・・・ n 09/2/7(土) 21:33 発言[未読]
【60297】Re:2つの条件でのVLOOKUPをマクロで・・・ モー 09/2/8(日) 17:41 質問[未読]
【60322】Re:2つの条件でのVLOOKUPをマクロで・・・ n 09/2/9(月) 22:55 発言[未読]
【60440】Re:2つの条件でのVLOOKUPをマクロで・・・ モー 09/2/21(土) 19:39 お礼[未読]

【60288】2つの条件でのVLOOKUPをマクロで・・・
質問  モー  - 09/2/7(土) 19:59 -

引用なし
パスワード
   《こんなことがしたいのですが・・・》
クラスと出席番号の2つの条件が一致する人名をマクロで一発表示する。

ファイル1にクラスと出席番号のみが記載されたリストがあり,これに人名を表示する。
ファイル2にテーブルリスト(ファイル名:テーブル.xls,シート名:テーブル)を用意しています。

LOOKUP関数では下記のようなイメージになります。

<ファイル1>クラスと出席番号の2つの条件が一致する人名を表示する側
VLOOKUPの検索値にクラスと出席番号を連結 A2&B2 したものを使用しています。
 
    A    B     C     
 ┌───┬────┬─────────
1|クラス|出席番号| 名前 
2|  1 |  3  |=IF(ISERROR(VLOOKUP(A2&"-"&B2,'C:\デスクトップ\[テーブル.xls]テーブル'!$A$2:$D$8,4,FALSE)),"",VLOOKUP(A2&"-"&B2,'C:\デスクトップ\[テーブル.xls]テーブル'!$A$2:$D$8,4,FALSE)
3|  2 |  1  |=IF(ISERROR(VLOOKUP(A3&"-"&B3,'C:\デスクトップ\[テーブル.xls]テーブル'!$A$2:$D$8,4,FALSE)),"",VLOOKUP(A3&"-"&B3,'C:\デスクトップ\[テーブル.xls]テーブル'!$A$2:$D$8,4,FALSE)
           ※#N/A! を表示させないために,=IF(ISERROR・・・を冒頭に使用しています。

 <ファイル2 テーブル.xls>テーブル側
A列にクラスと出席番号を連結した列を準備しています。
 
     A     B    C    D  
 ┌─────┬───┬────┬────┬─
1|  連結  |クラス|出席番号| 名前 |
2|=B2&"-"&C2|  1 |  1  |山中  |
3|=B3&"-"&C3|  1 |  2  |古賀  |
4|=B4&"-"&C4|  1 |  3  |島田  |
5|=B5&"-"&C5|  2 |  1  |池田  |
6|=B6&"-"&C6|  2 |  2  |寺田  |
7|=B7&"-"&C7|  2 |  3  |山内  |
8|=B8&"-"&C8|  2 |  4  |松本  |

《これを記録マクロでやってみると・・・》
ActiveCell.FormulaR1C1 = _
     "=IF(ISERROR(VLOOKUP(RC[-2]&""-""&RC[-1],'C:\デスクトップ\[テーブル.xls]テーブル'!R2C1:R999C4,4,FALSE)),"""",VLOOKUP(RC[-2]&""-""&RC[-1],'C:\デスクトップ\[テーブル.xls]テーブル'!R2C1:R999C4,4,FALSE))"
といった感じになりました。

ちなみにこれですと,1つのアクティブになっているセルにしか有効にならないため,
冒頭のActiveCell.FormulaR1C1 = のところを
Columns("C:C").FormulaR1C1 =  に変えてみました。

《問題・・・》
これで,うまくいくことはいくのですが・・・,
1.「値の更新:テーブル」というファイル指定のボックスが必ず現れる。
  ⇒このボックスを表示させずして,テーブルリストを参照しに行くことはできないでしょうか?
2.Columns("C:C").FormulaR1C1 =と指定してしまうと印刷したときに白紙ページが永遠と出てしまう。
  (表示はされないが,最後の行まで数式が入ってしまうためです。)
  実際にはファイル1のA列とB列にデータが入っている行までのC列をマクロで指定する方法があればよいのですが,
  そのような方法はありますか?
  印刷する際に印刷範囲を手動で指定し直すという方法もありますが,できれば自動化したいです。
  ちなみにファイル1のA列とB列のデータ入力行数は都度異なります。
3.その他,ほかによい方法があれば教えてください。

よろしくお願いします。

【60289】Re:2つの条件でのVLOOKUPをマクロで・・...
発言  n  - 09/2/7(土) 21:33 -

引用なし
パスワード
   >⇒このボックスを表示させずして,テーブルリストを参照しに行くことはできないでしょうか?
数式内の参照Bookのパスと名前が正しければ、通常は表示されません。
Bookのフルパスが正しいかどうか確認してください。

>実際にはファイル1のA列とB列にデータが入っている行までのC列をマクロで指定する方法
データ状態によっていろいろとありますが
Dim x As Long

With ActiveSheet
  x = .Cells(.Rows.Count, 1).End(xlUp).Row
  MsgBox .Range("C2:C" & x).Address(0, 0)
  MsgBox .Range("C2").Resize(x - 1).Address(0, 0)
  MsgBox .Range("C2", .Cells(.Rows.Count, 1).End(xlUp).Offset(, 2)).Address(0, 0)
  With .Range("A1").CurrentRegion
    MsgBox Intersect(.Cells, .Offset(1), .Columns("C")).Address(0, 0)
  End With
End With
...とか。

【60297】Re:2つの条件でのVLOOKUPをマクロで・・...
質問  モー  - 09/2/8(日) 17:41 -

引用なし
パスワード
   ▼n さん:
>>⇒このボックスを表示させずして,テーブルリストを参照しに行くことはできないでしょうか?
>数式内の参照Bookのパスと名前が正しければ、通常は表示されません。
>Bookのフルパスが正しいかどうか確認してください。
ありがとうございます。確認し,再度実行した結果表示されなくなりました。

>>実際にはファイル1のA列とB列にデータが入っている行までのC列をマクロで指定する方法
>データ状態によっていろいろとありますが
>Dim x As Long
>
>With ActiveSheet
>  x = .Cells(.Rows.Count, 1).End(xlUp).Row
>  MsgBox .Range("C2:C" & x).Address(0, 0)
>  MsgBox .Range("C2").Resize(x - 1).Address(0, 0)
>  MsgBox .Range("C2", .Cells(.Rows.Count, 1).End(xlUp).Offset(, 2)).Address(0, 0)
>  With .Range("A1").CurrentRegion
>    MsgBox Intersect(.Cells, .Offset(1), .Columns("C")).Address(0, 0)
>  End With
>End With

上記を実行して,結果はわかりました。
それぞれの行で,どういう処理がおこなわれているのか違いを説明していただけるとありがたいのですが。
よろしくお願いします。

ちなみに,
Sub Test()

Dim x As Long

With ActiveSheet
  x = .Cells(.Rows.Count, 1).End(xlUp).Row

End With
Range("C2:C" & x).FormulaR1C1 = _
     "=IF(ISERROR(VLOOKUP(RC[-2]&""-""&RC[-1],'D:\[テーブル.xls]テーブル'!R2C1:R999C4,4,FALSE)),"""",VLOOKUP(RC[-2]&""-""&RC[-1],'D:\[テーブル.xls]テーブル'!R2C1:R999C4,4,FALSE))"

End Sub

で,私がやりたかった結果は得られているような気がします。

【60322】Re:2つの条件でのVLOOKUPをマクロで・・...
発言  n  - 09/2/9(月) 22:55 -

引用なし
パスワード
   >それぞれの行で,どういう処理がおこなわれているのか違いを説明していただけるとありがたいのですが。
説明しても良いのですが、体感しながら調べていく方が身につきますよ。
適当なデータがあるシートで以下コードを試してみるとか。

Sub test()
  With ActiveSheet
    .Cells(.Rows.Count, 1).Select
    .Cells(.Rows.Count, 1).End(xlUp).Select
    .Cells(.Rows.Count, 1).End(xlUp).Offset(, 2).Select
    .Range("C2").Resize(10).Select
    With .Range("A1").CurrentRegion
      .Select
      Intersect(.Cells, .Offset(1)).Select
      Intersect(.Cells, .Offset(1), .Columns("C")).Select
    End With
  End With
End Sub

ステップ実行の仕方はわかりますか?
VBEコード内で[F8]キーを押す事で1ステップずつコード実行できます。
コードとワークシートの操作結果を見ながら理解してください。

また、VBEのヘルプは状況依存型です。
コード内の調査語句にマウスキャレットをあてて[F1]キーを押せば
目的のトピックにクイックアクセスできますので、色々調べてみてください。

【60440】Re:2つの条件でのVLOOKUPをマクロで・・...
お礼  モー  - 09/2/21(土) 19:39 -

引用なし
パスワード
   ▼n さん:
>ステップ実行の仕方はわかりますか?
>VBEコード内で[F8]キーを押す事で1ステップずつコード実行できます。
>コードとワークシートの操作結果を見ながら理解してください。
>
>また、VBEのヘルプは状況依存型です。
>コード内の調査語句にマウスキャレットをあてて[F1]キーを押せば
>目的のトピックにクイックアクセスできますので、色々調べてみてください。

試してみます。
ありがとうございました。

3738 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free