Excel VBA 質問スレッド No.21 (解決済)

データの取得についての質問です

投稿者 : まみこ     投稿日時 : 2017/03/27(Mon) 15:48:53     OS : Windows 8     EXCEL : Excel 2010
はじめまして
初心者ですがよろしくお願いします

マスタシートとワークシートの2つのシートがあります

1.マスターシートにはA列:商品の番号(4ケタ)とB列:商品名が登録してあります
(今は50品目ですが増えたり減ったりする可能性があります)

<マスターシート>
   A    B   C   D
1 1001  机
2 1002  椅子
3 1003  本棚
4 2001  電話 
・  ・
・  ・
・  ・

2.ワークシートはCSVファイルの値をA列に読み込んできます
(処理の度に件数は変わります)

<ワークシート>
   A    B   C   D
1 1001 
2 1001
3 2001
4 1003
5 1002
・  ・
・  ・
・  ・

3.やりたいこと
VBAで「VLOOKUP」を使って、ワークシートのA列の番号でマスターシートを
参照して、ワークシートのB列に商品名を入れたい。
(下の図のように)

<ワークシート>
   A    B   C   D
1 1001  机 
2 1001  机
3 2001  電話
4 1003  本棚
5 1002  椅子
・  ・    ・
・  ・    ・
・  ・    ・


あちらこちら見ながら以下のようなものを作りました。

Sheets("work").Select
Range("B1").Select

Dim number

For number = 1 To 10000 ← 何件あるかわからないのでとりあえず

Worksheets("work").Cells(number, 2) = _
"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],内線マスタ! R5C1:R100C2,2,FALSE)))"
Next number

これで取り敢えず結果は出るのですが、何件のデータがワークシートに読み込まれるか
わからないので「1 To 10000」としましたが、なにかあるとここを修正することに
なってしまいます

そこで、ワークシートに何件入力されても、後で修正しなくてもいいようにしたいです

ワークシートのデータがあるところまで選択できればよいとは思うのですが
方法がわかりません。

どうか教えていただけるとうれしいです。

よろしくおねがいします

[返信 1] Re : データの取得についての質問です
投稿者 : まみこ     投稿日時 : 2017/03/27(Mon) 15:57:49
■[質問] まみこさん(2017-03-27 15:48:53)の記事
> はじめまして
> 初心者ですがよろしくお願いします

> マスタシートとワークシートの2つのシートがあります

> 1.マスターシートにはA列:商品の番号(4ケタ)とB列:商品名が登録してあります
> (今は50品目ですが増えたり減ったりする可能性があります)

> <マスターシート>
>    A    B   C   D
> 1 1001  机
> 2 1002  椅子
> 3 1003  本棚
> 4 2001  電話 
> ・  ・
> ・  ・
> ・  ・

> 2.ワークシートはCSVファイルの値をA列に読み込んできます
> (処理の度に件数は変わります)

> <ワークシート>
>    A    B   C   D
> 1 1001 
> 2 1001
> 3 2001
> 4 1003
> 5 1002
> ・  ・
> ・  ・
> ・  ・

> 3.やりたいこと
> VBAで「VLOOKUP」を使って、ワークシートのA列の番号でマスターシートを
> 参照して、ワークシートのB列に商品名を入れたい。
> (下の図のように)

> <ワークシート>
>    A    B   C   D
> 1 1001  机 
> 2 1001  机
> 3 2001  電話
> 4 1003  本棚
> 5 1002  椅子
> ・  ・    ・
> ・  ・    ・
> ・  ・    ・


> あちらこちら見ながら以下のようなものを作りました。

> Sheets("work").Select
> Range("B1").Select

> Dim number

> For number = 1 To 10000 ← 何件あるかわからないのでとりあえず

> Worksheets("work").Cells(number, 2) = _
> "=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],内線マスタ! R5C1:R100C2,2,FALSE)))"
> Next number

> これで取り敢えず結果は出るのですが、何件のデータがワークシートに読み込まれるか
> わからないので「1 To 10000」としましたが、なにかあるとここを修正することに
> なってしまいます

> そこで、ワークシートに何件入力されても、後で修正しなくてもいいようにしたいです

> ワークシートのデータがあるところまで選択できればよいとは思うのですが
> 方法がわかりません。

> どうか教えていただけるとうれしいです。

> よろしくおねがいします

すみません。間違っていました

Worksheets("work").Cells(number, 2) = _
"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],内線マスタ! R5C1:R100C2,2,FALSE)))"
Next number



Worksheets("work").Cells(number, 2) = _
"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],商品マスタ! R1C1:R100C2,2,FALSE)))"
Next number

上記でした。

[返信 2] Re : データの取得についての質問です
投稿者 : 管理人     投稿日時 : 2017/03/27(Mon) 22:06:21
最終行を判別する方法はいくつかありますが、
Endプロパティを使えば、途中に空白行を含んでいたとしても最終行を判別できます。

Sub sample1()
Dim number As Long

With Worksheets("work")
' A列1行目からデータのある最終行まで繰り返し
For number = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(number, 2).FormulaR1C1 = _
"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],内線マスタ! R1C1:R100C2,2,FALSE)))"
Next number
End With
End Sub

[返信 3] Re : データの取得についての質問です
投稿者 : まみこ     投稿日時 : 2017/04/05(Wed) 18:41:53
管理者様

お返事が遅くなってすみませんでした。

ご教授ありがとうございました。withは使ったことがなかったので勉強になりました。

ただ、同じようなことなのですが
もうひとつ考えていることがありまして・・・

商品マスタからの検索なのですが、

"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],商品マスタ! R1C1:R100C2,2,FALSE)))"

この部分ですが、商品マスタ「R1C1:R100C2」としていますが、今は56行データあります。
ただ、4月に入り扱う商品数が増えることになったのですが、どれくらい増えるかがわかりません。

余裕を持って今は100行にしていますが、商品の増減がわからない場合に「xlUp」を使って
対応したように、増減や空白があっても対応できるようにできるのでしょうか?

追加の質問のようになってしまい、すみません。
ご教授願えるとうれしいです。

[返信 4] Re : データの取得についての質問です
投稿者 : 管理人     投稿日時 : 2017/04/06(Thu) 23:10:24
内線マスタも同様にEndプロパティで最終行を取得すればよいです。
そして、取得した最終行を数式部分の行数部分に文字列連結すればOKです。

前の回答は少しわかりにくかったかもしれないので、
withを使わずに書き直しました。


Sub sample170406()

Dim naisenLastRow As Long
Dim workLastRow As Long
Dim number As Long

' 内線マスタのA列最終行
naisenLastRow = Worksheets("内線マスタ").Cells(Rows.Count, 1).End(xlUp).Row

' workのA列最終行
workLastRow = Worksheets("work").Cells(Rows.Count, 1).End(xlUp).Row

' workのA列1行目から最終行まで繰り返し
For number = 1 To workLastRow

Worksheets("work").Cells(number, 2).FormulaR1C1 = _
"=IF(RC[-1]="""","""",(VLOOKUP(RC[-1],内線マスタ! R1C1:R" & naisenLastRow & "C2,2,FALSE)))"

Next number

End Sub

[返信 5] Re : データの取得についての質問です
投稿者 : まみこ     投稿日時 : 2017/04/10(Mon) 16:39:14
管理人様

ご教授ありがとうございました。

お陰様でデータの件数が増減しても問題がないような形に
することができました。

ひとつの問題の解決方法についてもいろいろな方法があるのですね・・・

勉強になりました。ありがとうございました。

また、よろしくお願いします。

スポンサード リンク
 
 
当掲示板について
返信入力フォーム
お 名 前  :
内  容   :
ステータス  : この質問を解決済みにする

認証コード  :
        キャプチャ画像


( 処理日時 : 2019-08-26 10:30:05 )

Page
Top