Excel VBA質問箱 IV

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

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


39780 / 76738 ←次へ | 前へ→

【42065】Re:1つのセルにVLOOKUP関数と入力規則を両方
発言  ichinose  - 06/8/30(水) 21:11 -

引用なし
パスワード
   ▼裕香 さん:
こんばんは。

>現在伝票入力フォームを作成中ですが、あるキーワードで蓄積データから検索し
>各セルにVLOOKUP関数を使って表示させます。表示させるセルには入力規則でリスト選択入力している箇所もあります。
>実際私のしたい事は、VLOOKUPで表示させた時、データが数字や文字ならそれを表示。
>もし、VLOOKUPで表示させたデータが「0」ゼロなら、入力規則のリスト選択が出来る様にしたいのです。
これは、シートのChangeイベントで↑このような動作になるようなコードを
記述しなければなりませんね!!



新規ブックのSheet1というシート(規定シート)のセルG1からH5に以下のような
表があるとします。

    G     H
 1  1     aaaaaa
 2  2     bbbbbb
 3  3     cccccc
 4  4     dddddd
 5  5        0


同じシートの

セルA1に

数字(1〜5)を入力すると、
対応したH列のデータをセルB1に表示します。
但し、上記のG1〜G5以外のデータがセルA1に入力された場合、
または、5を入力した場合の対応するH列のデータは、0のような場合は、

H1〜H4のリストでセルB1に入力規則を設定します。


まず、標準モジュールに
'==========================================================
Sub main()
  With ActiveSheet
    With .Range("g1:h5")
      .FormulaArray = "={1,""aaaaaa"";2,""bbbbbb"";" & _
              "3,""cccccc"";4,""dddddd"";5,0}"
             
      .Value = .Value
      End With
    End With
End Sub

上記を実行して、当該シートのG1:H5に表を作成してください。


次に上記の表を作成したシートモジュールに

'============================================================
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ans As Variant
  Dim rng As Range
  Set rng = Application.Intersect(Target, Range("a1"))
  If Not rng Is Nothing Then
    With rng.Offset(0, 1)
     .Validation.Delete
     .Select
     End With
    DoEvents
    rng.Select
    DoEvents
    rng.Offset(0, 1).Activate
    Application.EnableEvents = False
    ans = Application.VLookup(Target.Value, Range("g1:h4"), 2, False)
    If IsError(ans) Then ans = 0
    If ans <> 0 Then
     rng.Offset(0, 1).Value = ans
    Else
     With rng.Offset(0, 1).Validation
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$H$1:$H$4"
       End With
     End If
    Application.EnableEvents = True
    End If
   
End Sub


これで当該シートの
セルA1に1〜4を指定してください。

GI〜H1の表を検索し、該当する文字列がセルB1に表示されます。

セルA1に5、又は、G列にない文字を指定してください。
セルB1がリストH1:H4の入力規則が設定されます。

例題を試してみてください。

0 hits

【42055】1つのセルにVLOOKUP関数と入力規則を両方 裕香 06/8/30(水) 17:17 質問
【42065】Re:1つのセルにVLOOKUP関数と入力規則を両方 ichinose 06/8/30(水) 21:11 発言
【42078】Re:1つのセルにVLOOKUP関数と入力規則を両方 裕香 06/8/31(木) 8:38 お礼

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