Excel VBA質問箱 IV

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

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


7588 / 13644 ツリー ←次へ | 前へ→

【37997】複数条件の一致について だいすけ 06/5/24(水) 22:09 質問[未読]
【38003】Re:複数条件の一致について ハチ 06/5/25(木) 0:35 発言[未読]
【38006】Re:複数条件の一致について だいすけ 06/5/25(木) 7:09 発言[未読]
【38007】Re:複数条件の一致について だいすけ 06/5/25(木) 7:11 発言[未読]
【38015】Re:複数条件の一致について ハチ 06/5/25(木) 10:03 発言[未読]
【38018】Re:複数条件の一致について だいすけ 06/5/25(木) 10:49 発言[未読]
【38025】Re:複数条件の一致について だいすけ 06/5/25(木) 11:30 質問[未読]
【38027】Re:複数条件の一致について ハチ 06/5/25(木) 11:43 回答[未読]
【38029】Re:複数条件の一致について ハチ 06/5/25(木) 12:02 発言[未読]
【38034】Re:複数条件の一致について だいすけ 06/5/25(木) 13:01 発言[未読]
【38040】Re:複数条件の一致について ハチ 06/5/25(木) 13:45 発言[未読]
【38042】Re:複数条件の一致について だいすけ 06/5/25(木) 13:52 発言[未読]
【38043】Re:複数条件の一致について ハチ 06/5/25(木) 14:03 発言[未読]
【38044】Re:複数条件の一致について だいすけ 06/5/25(木) 14:13 発言[未読]
【38048】Re:複数条件の一致について ハチ 06/5/25(木) 14:38 発言[未読]
【38049】Re:複数条件の一致について Statis 06/5/25(木) 14:40 発言[未読]
【38050】Re:複数条件の一致について ハチ 06/5/25(木) 14:48 発言[未読]
【38052】Re:複数条件の一致について Statis 06/5/25(木) 15:01 発言[未読]

【37997】複数条件の一致について
質問  だいすけ  - 06/5/24(水) 22:09 -

引用なし
パスワード
   いつも大変お世話になっております。

今回このようなマクロを作成しました。

Sub test ()
Dim wb As Workbook
Dim ws, ws2 As Worksheet
Dim R, RR As Range
Dim wb_r As Long


wb_r = ActiveWorkbook.Worksheets("CVPARTS").Range("A1").Range("A65536").End(xlUp).Row
If wb_r <> 1 Then
wb_r = wb_r + 1


For Each R In Range(Worksheets("集めた目録").Range("A1:B65536"), Worksheets("集めた目録").Range("A1").Range("B65536").End(xlUp).Row)
  For Each ws2 In wb.Worksheets
    If InStr(1, ws2.Name, "マスター") <> 0 Then
      For Each RR In Range(ws.Range("D1:G65536", ws.Range("D1").Range("G65536").End(xlUp).Row)
        If R = RR Then
           wb.Worksheets("CVPARTS").Cells(wb_r, 1).Value = R.Value
           wb_r = wb_ + 1
        End If
      Next RR
    End If
  Next ws2
Next R
End Sub

上記のコードの目的は
1."集めた目録"というシートのAセルBセルの値を上から順に読む
2.このブックには"〜マスター"と名の付くシートが4枚ある
3.そのシートのGセルの値とHセルの値が、"集めた目録"シートの上から順に読んで いるAセル、Bセルに対応しているかを比較する
4.その結果両方とも一致していれば、"CVPARTS"の10行目から順に、
 "〜マスター"シートのDセルの値は"CVPARTS"シートのEセルに、Eセルの値はFセル に、Fセルの値はGセルに、Gセルの値はHセルに、Hセルの値はIセルにといったよ うに、表示させていきたいのですが・・・・

wb.Worksheets("CVPARTS").Cells(wb_r, 1).Value = R.Value
           wb_r = wb_ + 1
既に上記の時点で(というよりもっと前より)分からなくなっております。
お手数ですが、力をお貸しねがえないでしょうか?

【38003】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 0:35 -

引用なし
パスワード
   ▼だいすけ さん:

こんばんは
37952のマクロを再利用されているようですが、
どのくらいまでわかりましたか?
前回のマクロは比較に適した作りとは言い難いので
別の方法で作ることをお勧めします。

>wb_r = ActiveWorkbook.Worksheets("CVPARTS").Range("A1").Range("A65536").End(xlUp).Row
>If wb_r <> 1 Then
>wb_r = wb_r + 1

前回のマクロでは、任意のファイルを開いてA列に書き込む
でしたので開いたファイルのA列にデータがあった場合、
上書きしないように最終行+1の値を取得してました。
0行の場合も1となってしまう為、If wb_r <>1 で補正してました。

Rangeのヘルプを確認して引数には何を指定するのか、
もう一度、確認してください。
ちなみに
Range("A1").Range("A65536").End(xlUp).Row ならA列最終行のRowの値(数字)
Range("A1").Range("A65536").End(xlUp) ならA列最終セル(Range)になります。
Cellsの引数も確認してみてください。

>上記のコードの目的は
>1."集めた目録"というシートのAセルBセルの値を上から順に読む
>2.このブックには"〜マスター"と名の付くシートが4枚ある
>3.そのシートのGセルの値とHセルの値が、"集めた目録"シートの上から順に読んで いるAセル、Bセルに対応しているかを比較する
>4.その結果両方とも一致していれば、"CVPARTS"の10行目から順に、
> "〜マスター"シートのDセルの値は"CVPARTS"シートのEセルに、Eセルの値はFセル に、Fセルの値はGセルに、Gセルの値はHセルに、Hセルの値はIセルにといったよ うに、表示させていきたいのですが・・・・

CVPARTS、集めた目録、〜マスター(4つのSheets)の関係がよくわかりません。
集めた目録のAセル、Bセルを 〜マスターのGセル、Hセルと比較
一致すれば、CVPARTSの10行目から順にコピー?
マスターに4つには重複した項目はない?
結合セルは?

コピーするセルの相対もよくわかりません。
一致した場合は、
CVPARTSのEセル に 〜マスターのDセル


とづらづらと書いてみてください。

【38006】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 7:09 -

引用なし
パスワード
   ▼ハチ さん:
おはようございます。
毎回、色々と有難うございます。

今回まさに37952のマクロを使用してみましたが、うまく走りません。

>別の方法で作ることをお勧めします。
前回のような感じの応用では駄目っていうことですか??よければ、ヒントだけでもお教え願えませんか?

>CVPARTS、集めた目録、〜マスター(4つのSheets)の関係がよくわかりません。
>集めた目録のAセル、Bセルを 〜マスターのGセル、Hセルと比較
>一致すれば、CVPARTSの10行目から順にコピー?
>マスターに4つには重複した項目はない?
>結合セルは?
>
>コピーするセルの相対もよくわかりません。
>一致した場合は、
>CVPARTSのEセル に 〜マスターのDセル
>・
>・
>とづらづらと書いてみてください

ちなみに"集めた目録"シートは、前回教えて頂いた37952のマクロを使用して、
"目録.xls"から必要な値を
A       B
NV24356    商品名
NG23248    商品名
 ・      ・
 ・      ・
 ・      ・
 ・      ・
といった感じでAセルとBセルに対応した用に集めてきたシートです。

今回さらに、"集めた目録”シートがあるブックの中に
"CVPARTS"、
"ユニットマスター"、
"共通部品マスター"、
"その他マスター"、
"駆動系ますたー"、
"部品マスター"
といった、6つのシート+"集めた目録マスター"
が存在します。

この"〜マスター"シートの中のDセルとGセルに
D  ・  ・  G
NV24356    商品名
NG23248    商品名
 ・      ・
 ・      ・
 ・      ・
 ・      ・
といった感じに"集めた目録"シートのAセルとBセルに対応した物があります。

そこで、"集めた目録"シートのAセルBセルの二つのセルに一致した値を
"〜マスター"シートから検索して、存在していれば10行目から
"CVPARTS"シートのEセルに"〜マスタ"シートのDセルの値
"CVPARTS"シートのFセルに"〜マスタ"シートのEセルの値
"CVPARTS"シートのGセルに"〜マスタ"シートのFセルの値
"CVPARTS"シートのHセルに"〜マスタ"シートのGセルの値
"CVPARTS"シートのIセルに"〜マスタ"シートのHセルの値
"CVPARTS"シートのJセルに"〜マスタ"シートのIセルの値
"CVPARTS"シートのKセルに"〜マスタ"シートのJセルの値
"CVPARTS"シートのLセルに"〜マスタ"シートのKセルの値
             ・
             ・
             ・
             ・
"CVPARTS"シートのTセルに"〜マスタ"シートのSセルの値
まで、表示させていきたいのが今回の目的です。

ながながと書かせていただきましたが、どうか宜しくおねがい
いたします。

【38007】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 7:11 -

引用なし
パスワード
   >▼ハチ さん:
すみません、訂正です。
"集めた目録マスター"とありますが、"集めた目録"です。
すみませんです。

【38015】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 10:03 -

引用なし
パスワード
   ▼だいすけ さん:

>>別の方法で作ることをお勧めします。
>前回のような感じの応用では駄目っていうことですか??よければ、ヒントだけでもお教え願えませんか?

いろいろな方法があると思います。
1、"集めた目録".Aセル="〜マスター".Dセル And "集めた目録".Bセル="〜マスター".Gセル と
1行づつ比較していく方法

2、"集めた目録".Aセル & Bセル の値を "〜マスター".Dセル & Gセルから検索する方法

などなどです。

>>マスター4つには重複した項目はない?
>>結合セルは?

この部分はどうですか?


>そこで、"集めた目録"シートのAセルBセルの二つのセルに一致した値を
>"〜マスター"シートから検索して、存在していれば10行目から
>"CVPARTS"シートのEセルに"〜マスタ"シートのDセルの値
>"CVPARTS"シートのFセルに"〜マスタ"シートのEセルの値
>             ・
>             ・
>             ・
>             ・
>"CVPARTS"シートのTセルに"〜マスタ"シートのSセルの値
>まで、表示させていきたいのが今回の目的です。

ここは書けそうではないですか?
例として"CVPARTS"の10行目に 部品マスターの5行目 の内容をコピーするなら
Worksheets("CVPARTS").Range("E10:T10").Value = _
Worksheets("部品マスター").Range("D5:S5").Value
ですよね。

こんな感じで分解していって分からないところを洗い出したほうがいいと思います。
"マスター"を含むSheetsの選択とかは出来てますしね。

【38018】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 10:49 -

引用なし
パスワード
   ▼ハチ さん:
ハチさん色々とほんとにありがとう御座います。

なんか、頭がごちゃごちゃになってきて
どうすればよいか全然思い浮かびません・・・・

申し訳ないですT T

【38025】Re:複数条件の一致について
質問  だいすけ  - 06/5/25(木) 11:30 -

引用なし
パスワード
   ▼ハチ さん:
ハチさん、お願いします。
申し訳御座いません・・・・・

【38027】Re:複数条件の一致について
回答  ハチ  - 06/5/25(木) 11:43 -

引用なし
パスワード
   こんな感じでどうでしょ。
文字列をくっつけて検索にしてみました。
ちょっとですが注釈も入れたので解析してみてください。

Sub test()

Dim ws As Worksheet
Dim CV_r As Long 'CVPARTSのRow
Dim Mas_r As Long 'マスターのRow
Dim R As Range '集めた目録のRange
Dim F As Variant '検索用


'IV列に検索文字列作成
With Worksheets("集めた目録").Range(Range("A1"), Range("A65536").End(xlUp)).Offset(, 255)
  .Formula = "=CONCATENATE(A1,B1)"
  .Value = .Value
End With
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    With ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp)).Offset(, 255)
      .Formula = "=CONCATENATE(D1,G1)"
      .Value = .Value
    End With
  End If
Next ws

CV_r = 10 'CV_rは10行目から
'集めた目録のIV列をループ
For Each R In Range(Worksheets("集めた目録").Range("IV1"), Worksheets("集めた目録").Range("IV65536").End(xlUp))
  'マスターを含むWorkSheetsをループ
  For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "マスター") <> 0 Then
      'マスターIV列でRを検索してあったら、CVRARTSに書き込み
      Set F = ws.Range("IV:IV").Find(R.Value, , xlValues, xlWhole)
      If Not F Is Nothing Then
        With Worksheets("CVPARTS")
          .Range(.Cells(CV_r, 5), .Cells(CV_r, 19)).Value = _
          ws.Range(ws.Cells(F.Row, 4), ws.Cells(F.Row, 18)).Value
        End With
        'CV_r を1行移動
        CV_r = CV_r + 1
        Exit For
      End If
    End If
  Next ws
Next R
'IV列を削除
Worksheets("集めた目録").Range("IV:IV").Clear
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    ws.Range("IV:IV").Clear
  End If
Next ws

End Sub

【38029】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 12:02 -

引用なし
パスワード
   >Dim Mas_r As Long 'マスターのRow

↑これ要らないです。
各シートのIV列は作業列として空けておいてください。

【38034】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 13:01 -

引用なし
パスワード
   ▼ハチ さん:
大変なものを、ありがとうございました!

早速走らせてみたのですが、
With Worksheets("集めた目録").Range(Range("A1"), Range("A65536").End(xlUp)).Offset(, 255)
この部分で、引っかかってしまいます。

あと、各シートのI列、とV列をあけないといけないのですが、
”〜マスター”シートは埋まっています。

ほんとすみません・・・

【38040】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 13:45 -

引用なし
パスワード
   ▼だいすけ さん:
>▼ハチ さん:
>大変なものを、ありがとうございました!
>
>早速走らせてみたのですが、
>With Worksheets("集めた目録").Range(Range("A1"), Range("A65536").End(xlUp)).Offset(, 255)
>この部分で、引っかかってしまいます。

自動改行が入ってたからでしょうか?
もしくは、実行時の選択しているSheetが良くなかったか・・・
下のマクロで貼りなおしてください。

>あと、各シートのI列、とV列をあけないといけないのですが、
>”〜マスター”シートは埋まっています。

I列とV列 ではなく、「IV列」です。
シートの一番右端の255列目のところですが、使ってます?


Sub test()

Dim ws As Worksheet
Dim CV_r As Long 'CVPARTSのRow
Dim R As Range '集めた目録のRange
Dim F As Variant '検索用


'IV列に検索文字列作成
With Worksheets("集めた目録").Range(Worksheets("集めた目録").Range("A1"), _
Worksheets("集めた目録").Range("A65536").End(xlUp)).Offset(, 255)

  .Formula = "=CONCATENATE(A1,B1)"
  .Value = .Value
End With
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    With ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp)).Offset(, 255)
      .Formula = "=CONCATENATE(D1,G1)"
      .Value = .Value
    End With
  End If
Next ws

CV_r = 10 'CV_rは10行目から
'集めた目録のIV列をループ
For Each R In Range(Worksheets("集めた目録").Range("IV1"), _
Worksheets("集めた目録").Range("IV65536").End(xlUp))
  'マスターを含むWorkSheetsをループ
  For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "マスター") <> 0 Then
      'マスターIV列でRを検索してあったら、CVRARTSに書き込み
      Set F = ws.Range("IV:IV").Find(R.Value, , xlValues, xlWhole)
      If Not F Is Nothing Then
        With Worksheets("CVPARTS")
          .Range(.Cells(CV_r, 5), .Cells(CV_r, 19)).Value = _
          ws.Range(ws.Cells(F.Row, 4), ws.Cells(F.Row, 18)).Value
        End With
        'CV_r を1行移動
        CV_r = CV_r + 1
        Exit For
      End If
    End If
  Next ws
Next R
'IV列を削除
Worksheets("集めた目録").Range("IV:IV").Clear
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    ws.Range("IV:IV").Clear
  End If
Next ws

End Sub

【38042】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 13:52 -

引用なし
パスワード
   ▼ハチ さん:
たびたびすみません、ありがとう御座います!

IV列は空いていますので、IV列には
セルの値をまとめる事ができました!

その次なんですが、CVPARTSへ一致したものをコピー出来ません。
色々やってみてるのですが。。。。

【38043】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 14:03 -

引用なし
パスワード
   ▼だいすけ さん:
>▼ハチ さん:
>たびたびすみません、ありがとう御座います!
>
>IV列は空いていますので、IV列には
>セルの値をまとめる事ができました!
>
>その次なんですが、CVPARTSへ一致したものをコピー出来ません。
>色々やってみてるのですが。。。。

確認ですが、なにかエラーが出るってことですか?
エラーはでないけどコピーされない?

最初にも書きましたが結合セルはないですよね?

文字列は完全一致で比較しているので半角、全角、大文字、小文字も区別します。

集めた目録 と 各マスターの IV列に値が入ってきている?

【38044】Re:複数条件の一致について
発言  だいすけ  - 06/5/25(木) 14:13 -

引用なし
パスワード
   ▼ハチ さん:
IVセルにはちゃんと値が入っております!
おそらく完全一致ではないからかもしれません。

例えば空白の位置の違いであったり、大文字小文字の違いも
ありそうです…

何文字以上一致とかで、出来ないでしょうか?

【38048】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 14:38 -

引用なし
パスワード
   ▼だいすけ さん:
>▼ハチ さん:
>IVセルにはちゃんと値が入っております!
>おそらく完全一致ではないからかもしれません。
>
>例えば空白の位置の違いであったり、大文字小文字の違いも
>ありそうです…
>
>何文字以上一致とかで、出来ないでしょうか?

んーー それは比較して一致しているとは言わないと思いますが・・・
何文字以上とかなら検索する仕様を変更して一から作り直しですね。

とりあえず、
「半角全角」「大文字小文字」「前後と間の空白の数」
「A列B列の値を含む(ちょっと微妙)」まで対応しました。

Sub test2()

Dim ws As Worksheet
Dim CV_r As Long 'CVPARTSのRow
Dim R As Range '集めた目録のRange
Dim F As Variant '検索用


'IV列に検索文字列作成
With Worksheets("集めた目録").Range(Worksheets("集めた目録").Range("A1"), _
Worksheets("集めた目録").Range("A65536").End(xlUp)).Offset(, 255)

  .Formula = "=TRIM(CONCATENATE(A1,B1))"
  .Value = .Value
End With
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    With ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp)).Offset(, 255)
      .Formula = "=TRIM(CONCATENATE(D1,G1))"
      .Value = .Value
    End With
  End If
Next ws

CV_r = 10 'CV_rは10行目から
'集めた目録のIV列をループ
For Each R In Range(Worksheets("集めた目録").Range("IV1"), _
Worksheets("集めた目録").Range("IV65536").End(xlUp))
  'マスターを含むWorkSheetsをループ
  For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "マスター") <> 0 Then
      'マスターIV列でRを検索してあったら、CVRARTSに書き込み
      Set F = ws.Range("IV:IV").Find(R.Value, , , xlPart, , , False, False)
      If Not F Is Nothing Then
        With Worksheets("CVPARTS")
          .Range(.Cells(CV_r, 5), .Cells(CV_r, 19)).Value = _
          ws.Range(ws.Cells(F.Row, 4), ws.Cells(F.Row, 18)).Value
        End With
        'CV_r を1行移動
        CV_r = CV_r + 1
        Exit For
      End If
    End If
  Next ws
Next R
'IV列を削除
Worksheets("集めた目録").Range("IV:IV").Clear
For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "マスター") <> 0 Then
    ws.Range("IV:IV").Clear
  End If
Next ws

End Sub

【38049】Re:複数条件の一致について
発言  Statis  - 06/5/25(木) 14:40 -

引用なし
パスワード
   こんにちは
横から失礼します。
「集めた目録」のシートのIV列(関数で結合したセル)と「マスター」を含む
すべてのシートのIV列(関数で結合したセル)を検索して一致したとき
「CVPARTS」シートへ書き込むのなら「Exit For」いらないのでは?
検索値が何処かひとつのシートにしかないのであればそのままで良いのですが。

勘違いならすみません。

【38050】Re:複数条件の一致について
発言  ハチ  - 06/5/25(木) 14:48 -

引用なし
パスワード
   ▼Statis さん:
>こんにちは
>横から失礼します。
>「集めた目録」のシートのIV列(関数で結合したセル)と「マスター」を含む
>すべてのシートのIV列(関数で結合したセル)を検索して一致したとき
>「CVPARTS」シートへ書き込むのなら「Exit For」いらないのでは?
>検索値が何処かひとつのシートにしかないのであればそのままで良いのですが。
>
>勘違いならすみません。

Statisさんフォローありがとうございます。
ちょっと自分の実力ではそろそろ限界っぽいです><

>>マスター4つには重複した項目はない?
>>結合セルは?

この質問の答えがなかったので速度Upの為にExitさせていたのですが。
どうでしょうね?

【38052】Re:複数条件の一致について
発言  Statis  - 06/5/25(木) 15:01 -

引用なし
パスワード
   ▼ハチ さん:
こんにちは
わざわざありがとう御座います。
>>マスター4つには重複した項目はない?
と言う事ならExitは必要ですね。

>>結合セルは?
一番厄介なものですね(特にマクロには)

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