エクセルxbs選択肢のインデックス

質問

EXCEL入力規則の「リスト」で元の値がセルに範囲指定(例：’シート名’$c$1:$c$10)の際に出力される選択肢(例の場合c1~c10の内容)より値を選択した際のインデックスを求めたいのですが、 ご教示お願い致します。

回答No.12

kkkkkm · ベストアンサー

> 作り方がまずったのか

何度も言いますが、こちらではどのようにしたのか不明ですので異常処理に飛んだとか言われてもわかりません。
何度も言いますが、Testがまともに動くのを確認してから
Private Sub Worksheet_Change(ByVal Target As Range)
に移行したほうがいいんじゃないですか。

何度も何度もいいますが、こちらではそちらのコードは見えないので、飛んだとか爆発したとか意味不明です。

お礼

色々検討頂きましたが、ユーザの一言で選択方式は無くなり、固定値設定に落ち着きました。
ご指導いただきありがとうございました。
貴重なお時間を頂きまして有難うございました。
大変勉強になりました。

回答No.13

imogasi

＃６です。
質問の大切な点が、質問に書いてなかったですね。
補足文章で、ややわかりましたが、
Sheet2のB列に当たるコードは、日本語のカナのEBICDICコードの１６進表記を文字列で表記したものらしい。
例えば
87　キ
BE 　゛
BD　ン
BC 　サ
BE　゛
のようです。
https://software.fujitsu.com/jp/manual/manualfiles/m130010/b1fw5992/01z200/b5992-c-00-00.html
参照
SjisコードからEBICDICに変換するソフトを、VBAなどで使える環境なら良いが、
自作するのは手間と知識がかかります。
選択(指定・入力）の道具は規則はエクセルのものを使うとして、選択されたカナ文字列をコード文字列にするのが、本質問の難しいところでしょう。この点を顕わにしてない。
WEB記事で変換のやり方もたやすくは見つからないようです。それにどの程度までの文字を変換するのかも書いてない。質問内容の説明が全般に不十分です。
カナだけならテーブルを作って合成できなくもないように思うが。
カナだけなら、上記のような表を見て、質問者が、テーブルを自作しますか？
ー
EBCDICコードと聞いて、５０年以上前の、若き日の、大型機では、IBM全盛時代のことが思い出されます。
今はパソコン時代なので、作った（必要としている大型機などのソフト担当）部署に頼んでSJISコードやUTFー８コードに変換するソフトを動かしてそれを送ってもらえない（またはSJISコードを使ってもらえないか）のを頼めないのかな。
＝＝
もう一度別質問しなおした方がよいのでは。

回答No.11

kkkkkm

> If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
> をブレークポイントにし、実行したら爆発した

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Test '←ここに呼び出したいプロシージャ名を入れる
End If
Application.EnableEvents = True
End Sub

として、そこをブレークポイントにして何が爆発するのでしょう。
そこで止まってなにも実行されないはずですが。
A1:A10に入力規則の「リスト」を設定しているということですか？

test()
単体でうまく動くかどうか確認したのでしょうか？

また、範囲指定が一定なら

With Range("A1").Validation
mList = Right(.Formula1, Len(.Formula1) - 1)
End With

でリスト範囲を取得する必要はありません。

Set mRng = Sheets("シート名").Range("C1:C10")

で済みます。

どちらにしても、どこに入力規則のリストを作ってリスト指定範囲がどこで、どのようにカスタマイズしたのか不明です。

補足

作り方がまずったのかIf文にブレイクし、止まり、ワンステップ実行したら次処理に行かず異常処理に飛びました

回答No.10

kkkkkm

ActiveXコントロールのコンボボックスを利用するとコードは短くなります。

コンボボックス(ActiveXコントロール)をシートに配置する
http://www4.synapse.ne.jp/yone/excel2010/excel2010_x_combobox.html

プロパティで
ListFillRange
を
Sheet2!C1:C10
にして

Private Sub ComboBox1_Change()
Range("B2").Value = Me.ComboBox1.ListIndex + 1
End Sub

また、コードを出したい場合には

プロパティで
ListFillRange
を
Sheet2!C1:D10
にして

D列にコードを入れて

Private Sub ComboBox1_Change()
Range("B2").Value = Me.ComboBox1.List(ComboBox1.ListIndex, 1)
End Sub

にするか

プロパティで
BoundColumnを2にして

Private Sub ComboBox1_Change()
Range("B2").Value = Me.ComboBox1.Value
End Sub

補足

ひとつ前に頂いたパターンをカスタマイズし、
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
をブレークポイントにし、実行したら爆発した

回答No.9

kkkkkm

> また、5番目に5を返すパターンが必要でしたらD1:D10に上から順に1から番号を振っておけば選択した選択肢の番目が取得できます。

上記は間違いで、D1:D10に何も入れなくて
Test2を実行すれば何番目かが取得できます。

> D1:D10にカナ文字に対応するコードを記載しておいて
の場合は以下で

Sub Test3()
Dim mCode As Long, mRow As Long
Dim mList As String
Dim mRng As Range

With Range("A1").Validation
mList = Right(.Formula1, Len(.Formula1) - 1)
End With
Set mRng = Evaluate(mList)
If Range("A1").Value <> "" Then
mRow = Application.Match(Range("A1"), mRng, 0)
mCode = mRng.Cells(1, 1).Offset(mRow - 1, 1).Value
Range("B1").Value = mCode
End If
Set mRng = Nothing
End Sub

回答No.8

kkkkkm

> 5番目を選択したからと言って、５や４を返してくれないのですね？

選択されたデータと元の選択肢リストとを順に比較して一致したらその順番を返しますから5番目を選択して実行したら、最初を0としてますから4が返ります。5を返したければ

For i = 1 To mRng.Rows.Count
If Range("A1").Value = mRng.Cells(i, 1).Value Then
に変更してください。

あと

> EXCELでカナ表示はできるみたいなので、カナ文字試用項目は選択項目にし、選択肢よりインデックスを求めて、そのインデックス番目の選択肢に合ったebcdicコードテーブルの文言を引っ張ってくると言う処理を実現したいのです。

インデックスを取らなくても
D1:D10にカナ文字に対応するコードを記載しておいて以下のようにするとコードを取得できますがいかがでしょう。
また、5番目に5を返すパターンが必要でしたらD1:D10に上から順に1から番号を振っておけば選択した選択肢の番目が取得できます。

Set mRng = Evaluate(mList)のあたりは前回までの回答を参考にして適宜変更してください。

Sub Test2()
Dim mCode As Long
Dim mList As String
Dim mRng As Range

With Range("A1").Validation
mList = Right(.Formula1, Len(.Formula1) - 1)
End With
Set mRng = Evaluate(mList)
If Range("A1").Value <> "" Then
mCode = Application.Match(Range("A1"), mRng, 0)
Range("B1").Value = mCode
End If
Set mRng = Nothing
End Sub

なお、選択した途端に実行したいのでしたら
リストのあるシートモジュールに
リストはA1とした場合

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("A1").Address Then
Test '←ここに呼び出したいプロシージャ名を入れる
End If
Application.EnableEvents = True
End Sub

補足

とりあえず
やってみます。

回答No.7

kkkkkm

> 質問ですが、これは何と何の比較になりますか？
> > If Range("A1").Value = mRng.Cells(i + 1, "A").Value Then

A1がリストと仮定してますので

Range("A1").Value
リストで選択して表示されているデータ

mRng.Cells(i + 1, "A").Value
mRngはリストの選択肢のセル範囲になります。
’シート名’$c$1:$c$10
で
その後に.Cells(行,列)と繋げると範囲の中での位置指定になります。
(以下シート名は略しています)
Range("C1:C10").Cells(1,1).Value
は
C1の値。
Range("C1:C10").Cells(2,1).Value
は
C2の値を返します。

ですので、A1の値とリストの値を上から順に比較しています。

Range("C1:C10").Cells(1,1)
はシート関数のイメージだと
=INDEX(C1:C10,1,1)
のようなものです。

補足

やはり比較が必要なのですね？
5番目を選択したからと言って、５や４を返してくれないのですね？

回答No.6

imogasi

質問の意味がつかめていません。
xbsはファイルの拡張子ですか？拡張子xbsの意味をGoogleで調べてみましたが、この意味ですか？
下記は、大きく見当はずれかもしれませんが、その際はすみません。
VBAを使うなら、
データ例　Sheet2のD1:D10に
a　以下何の文字列でもよい
s
d
f
g
h
j
k
b
n
Sheet1のA1:A10にデーター入力規則ーリスト
元の値　=Sheet2!D$1:D$10
====
VBEでSheet1のChangeイベントで
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
MsgBox Target
With Worksheets("Sheet2")
x = WorksheetFunction.Match(Target, .Range("D1:D10"), 0)
MsgBox x
Worksheets("Sheet1").Cells(Target.Row, "B") = x
End With
Application.EnableEvents = True
End If
End Sub
=====
操作 例
Sheet1のA列A1:A10間で「g」を選ぶとB列同行に５とセットされる。

補足

ごめんなさい。パンチミスです。気が付きませんでした。xlsの間違いです。やりたいことはこんな感じです。
　Ａ B
名前 EBCDICコード
ｷﾞﾝｻﾞ 87BEBD8CBE40404040404040404040
ﾄｳｷﾖｳ 958387AC8340404040404040404040
ﾁﾊﾞ 929DBE404040404040404040404040
ｵｵｻｶ 85858C864040404040404040404040

リスト：A2:A５
A2ｷﾞﾝｻﾞを選択したら、B2のEBCDICコードを取得したい

回答No.5

kkkkkm

No.4の補足です。

No.4にしても

Set mRng = Evaluate(mList)
でエラーになった場合は

No.4に変更した状態で

Set mRng = Evaluate(mList)
を
If InStr(mList, "!") > 0 Then
Set mRng = Sheets(Split(mList, "!")(0)).Range(Split(mList, "!")(1))
Else
Set mRng = Range(mList)
End If
に変更してみてください。

補足

丁寧なご回答ありがとうございます。
質問ですが、これは何と何の比較になりますか？
> If Range("A1").Value = mRng.Cells(i + 1, "A").Value Then
内情をもう少し詳細に話すと、ロケール＝英語(アメリカ)、言語もすべて英語関係にし、電文項目を入力し、EBCDICコードに変換する。と言う処理なのですが、電文内にカナ文字があり、これをマクロ内部でCellsで取り込むと当然変換できないので"?"が入ってきます。
EXCELでカナ表示はできるみたいなので、カナ文字試用項目は選択項目にし、選択肢よりインデックスを求めて、そのインデックス番目の選択肢に合ったebcdicコードテーブルの文言を引っ張ってくると言う処理を実現したいのです。

回答No.4

kkkkkm

No.2No.3の補足です。

> ’シート名’$c$1:$c$10

がVBAで通るかどうかわからないのでもしエラーになるようでしたら
(シート名!$C$1:$C$10が今までの表記でしたので)

With Range("A1").Validation
mList = Right(.Formula1, Len(.Formula1) - 1)
End With
の後に以下を追加してみてください。
mList = Replace(Right(mList, Len(mList) - 1), "'", "!")

回答No.3

kkkkkm

No.2の補足です。

今回の場合
mRng.Cells(i + 1, "A").Value
は、つい癖で上記のように記載しましたが
mRng.Cells(i + 1, 1).Value
の方が誤解がないかもしれません。