Excel VBA質問箱 IV

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

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


2401 / 13645 ツリー ←次へ | 前へ→

【68234】同一ワークシート内の参照先から単価を表示する方法 リョウ 11/2/17(木) 0:45 質問[未読]
【68245】Re:同一ワークシート内の参照先から単価を... りん 11/2/17(木) 12:24 回答[未読]
【68250】Re:同一ワークシート内の参照先から単価を... めいぷる 11/2/17(木) 15:19 発言[未読]
【68252】Re:同一ワークシート内の参照先から単価を... Yuki 11/2/17(木) 16:18 発言[未読]

【68234】同一ワークシート内の参照先から単価を表...
質問  リョウ  - 11/2/17(木) 0:45 -

引用なし
パスワード
   「商品番号と部品番号を入力すると単価を表示する表」をVBAで作っています。
・ワークシートのA列に商品番号、B列に部品番号が入っており、C列に単価を
表示したいと考えています。
・同じワークシート内に参照先(H列〜J列)があり、H列に商品番号、I列に番号、
J列にサイズが書かれています。

この表を使ってマクロを書くのに、どのような構成にするか考えたのが次の通りです。
a. A列に入力された商品番号が表のH列に無かった場合→C列に「再確認」と表示
b. A列に入力された商品番号が表のH列にあった場合→その右セル(I列)に書かれた部品番号がB列と一致するかどうか判断。
  △B列とI列が一致した場合で、J列が"大"の場合はC列に1000を表示。
  ▲B列とI列が一致した場合で、J列が"小"の場合はC列に500を表示。
  ▽B列とI列が一致しなかった場合は、C列に「サイズを選択」と表示

If_ElseIfやSelect Caseの入れ子にする方法を試しているのですが、調べながら試行錯誤しておりますがうまくいきません。
特にa.の「A列の値がH列にあるか」をVLOOKUPで書き、ヒットした場合は
「B列の値がI列の値に一致するか」という方法で進める方が良いのでしょうか。

知識がなく誠にお恥ずかしいのですが、何かご教示願えませんでしょうか。
以下は自分なりに書いてみた物ですが、誤りだらけの為に参考にはならないと思います。
---------------------
For I = 1 To 100

商品番号 = .Cells(I, 1) '本来はA列全体にしたいのですが書き方が分かりません。
部品番号 = .Cells(I, 2)
サイズ = .Cells(I, 10)
単価 = .Cells(I, 3)

  If 商品番号 = Cells("H1:H100") Then 'もし商品番号がH列にあったら
      Select Case 部品番号
      Case "123" '部品番号が123の時
        If パーツ番号 = ("J1:J100") Then 'もしパーツ番号が上のセルの右にあったら
          If サイズ = 大 Then
          単価 = 12000
          ElseIf サイズ = 小 Then
          単価 = 8000
          End If
        End If
      
      Case Else
      単価 = 0
      
    End Select

  ElseIf パーツ番号 もしパーツ番号がH列に無かったら
  単価 = "サイズを選択"
----------------------------
このような状態で申し訳ないのですが、ご教示お願い致します。

【68245】Re:同一ワークシート内の参照先から単価...
回答  りん E-MAIL  - 11/2/17(木) 12:24 -

引用なし
パスワード
       リョウ さん、こんにちは。

>「商品番号と部品番号を入力すると単価を表示する表」をVBAで作っています。
>・ワークシートのA列に商品番号、B列に部品番号が入っており、C列に単価を
>表示したいと考えています。
>・同じワークシート内に参照先(H列〜J列)があり、H列に商品番号、I列に番号、
>J列にサイズが書かれています。

>a. A列に入力された商品番号が表のH列に無かった場合→C列に「再確認」と表示
検索を使うと楽かも。

>b. A列に入力された商品番号が表のH列にあった場合→その右セル(I列)に書かれた部品番号がB列と一致するかどうか判断。
こっちは比較でよいかも。

>  △B列とI列が一致した場合で、J列が"大"の場合はC列に1000を表示。
>  ▲B列とI列が一致した場合で、J列が"小"の場合はC列に500を表示。

>  ▽B列とI列が一致しなかった場合は、C列に「サイズを選択」と表示

完全一致検索を記録して、分岐を入れるとこんな感じです。

Sub test()
  Dim r1 As Range, v1 As Variant, a1 As String, Rmax As Long, ws As Worksheet
  '現在表示しているシートが対象です。
  Set ws = Application.ActiveSheet
  'A列の一番下
  With ws.UsedRange
    Rmax = ws.Cells(.Cells(.Count).Row + 1, "A").End(xlUp).Row
  End With
  '2行目から繰り返す(1行目が見出しとして)
  For II = 2 To Rmax
    With ws
      v1 = Chr(0) '変数初期化
      Set r1 = .Columns("H").Find(.Cells(II, "A").Value, LookAt:=xlWhole)
      '検索結果から分岐
      If r1 Is Nothing Then
        v1 = "再確認" 'ヒットせず
      Else
        a1 = r1.Address '最初に見つかった位置を憶えておくため
        Do
          If .Cells(II, "B").Value = r1.Offset(0, 1).Value Then
            'AB一致でJの分岐 (疑問)単価は固定なのですか?
            Select Case r1.Offset(0, 2).Value
              Case "大": v1 = 1000
              Case "小": v1 = 500
              Case Else: v1 = "大小不明"
            End Select
            'ループ脱出
            Exit Do
          End If
          '同じ条件で繰り返す
          Set r1 = .Columns("H").FindNext(r1)
        Loop While Not r1 Is Nothing And r1.Address <> a1
        'Bが一致しないまま検索終了
        If v1 = Chr(0) Then v1 = "サイズを選択" '(疑問)商品番号では?
      End If
      '結果
      .Cells(II, "C").Value = v1
    End With
  Next
  'おわり
  Set r1 = Nothing: Set ws = Nothing
End Sub

フィルタとかDictionaryの方が速そうですが、件数があまり多くないならこれくらいでも処理の遅さは気にならないかなと。

【68250】Re:同一ワークシート内の参照先から単価...
発言  めいぷる  - 11/2/17(木) 15:19 -

引用なし
パスワード
   ▼リョウ さん:
こんにちは。

>「商品番号と部品番号を入力すると単価を表示する表」をVBAで作っています。
>・ワークシートのA列に商品番号、B列に部品番号が入っており、C列に単価を
>表示したいと考えています。
>・同じワークシート内に参照先(H列〜J列)があり、H列に商品番号、I列に番号、
>J列にサイズが書かれています。
>

A列、B列ともに手入力するのでしょうか?
通常は、H列に「商品番号」、I列に「部品番号」とでも名前定義をして、
A列、B列の入力規則でそれぞれの名前定義を設定しておけば、
プルダウンリストから選択するだけなので
>A列に入力された商品番号が表のH列に無かった場合→C列に「再確認」と表示
のチェックは不要です。

>B列とI列が一致しなかった場合
についてですが、H列とI列には相関関係はありますか?
(選んだ商品番号によって、次に選べる部品番号が決まる?)
もし、商品番号と部品番号がセットなら入力するのはA列だけでよく
(しかも入力規則を設定しておけばプルダウンから選ぶだけ)、
B列、C列はVLOOKUP関数をセットしておくだけでいいと思います。

H列とI列に関連性が無い場合は、B列にもI列の名前定義で入力規則を設定して
プルダウンから選択入力し、
C列にのみ数式を設定すればよいのでは。
(VLOOKUPでJ列の値を取得してそれが"大"なら1000を設定、"小"なら500を設定とする数式)

VBAで行わなければならない理由がありますか?

【68252】Re:同一ワークシート内の参照先から単価...
発言  Yuki  - 11/2/17(木) 16:18 -

引用なし
パスワード
   ▼リョウ さん:
>「商品番号と部品番号を入力すると単価を表示する表」をVBAで作っています。
>・ワークシートのA列に商品番号、B列に部品番号が入っており、C列に単価を
>表示したいと考えています。
>・同じワークシート内に参照先(H列〜J列)があり、H列に商品番号、I列に番号、
>J列にサイズが書かれています。
>
>この表を使ってマクロを書くのに、どのような構成にするか考えたのが次の通りです。
>a. A列に入力された商品番号が表のH列に無かった場合→C列に「再確認」と表示
>b. A列に入力された商品番号が表のH列にあった場合→その右セル(I列)に書かれた部品番号がB列と一致するかどうか判断。
>  △B列とI列が一致した場合で、J列が"大"の場合はC列に1000を表示。
>  ▲B列とI列が一致した場合で、J列が"小"の場合はC列に500を表示。
>  ▽B列とI列が一致しなかった場合は、C列に「サイズを選択」と表示

疑問点はH列I列が同じで大と小が各々あるときはどうするのかな?
疑問点は置いておいて
ディクショナリで処理をしてみました。


Sub TestA()
  Dim v1   As Variant
  Dim v2   As Variant
  Dim Dic1  As Object
  Dim Dic2  As Object
  Dim i    As Long
  
  With Worksheets("Sheet1")
    v1 = .Range("H1:J" & .Range("H" & .Rows.Count).End(xlUp).Row).Value
    v2 = .Range("A1:C" & .Range("B" & .Rows.Count).End(xlUp).Row).Value
  End With
  Set Dic1 = CreateObject("Scripting.Dictionary")
  Set Dic2 = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(v1)
    Dic1(v1(i, 1)) = Empty
    Select Case v1(i, 3)
      Case "大"
        Dic2(v1(i, 1) & vbTab & v1(i, 2)) = 1000
      Case "小"
        Dic2(v1(i, 1) & vbTab & v1(i, 2)) = 500
      Case Else
        Dic2(v1(i, 1) & vbTab & v1(i, 2)) = 0
    End Select
  Next
  For i = 1 To UBound(v2)
    If Dic1.Exists(v2(i, 1)) = True Then
      If Dic2.Exists(v2(i, 1) & vbTab & v2(i, 2)) = True Then
        v2(i, 3) = Dic2(v2(i, 1) & vbTab & v2(i, 2))
      Else
        v2(i, 3) = "サイズを選択"
      End If
    Else
      v2(i, 3) = "再確認"
    End If
  Next
  Worksheets("Sheet1").Range("A1").Resize(UBound(v2), 3).Value = v2
End Sub

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