テキストボックスを使ったときの条件分岐について

質問

テキストボックスを使ったときの条件分岐について
　ユーザーフォームを使って人事管理をしたいと思っています。　そこで、通勤距離を入力すると、通勤手当額が表示されるようにしたいのですがうまくいきません。手当額は変更があるために、エクセルのシートに入力します。
　シートのA１からA５まで２，５、１０、１５、・・・という風に距離数が入ります。
　　　　　B１からB５まで2,000、4,000、4,500、5,000、・・・と金額が入ります。
　ユーザーフォームにテキストボックスを二つ置き、ボックス１に距離を入力するとボックス2に金額が表示という風にしたいのです。
　2キロ未満は０、2キロ以上は2,000、5キロ以上は4,000、10キロ以上は4,500、15キロ以上は5,000という風に表示できるでしょうか？
　本を片手にやっているのですが、何分力不足で八方ふさがりになってしまいまいた。
　よろしくお願いいたします。

回答No.5

Masa2072 · ベストアンサー

回答No.１のものです。

前回サンプルを以下のように変更してみてください。
Private Sub TextBox1_Change()
　If IsNumeric(Me.TextBox1) Then
　　If Me.TextBox1 <= 2 Then
　　　Me.TextBox2 = 0
　　Else
　　　Me.TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1) - 0.1, Sheet1.Range("A1:B4"), 2)
　　End If
　Else
　　Me.TextBox2 = "No Numeric!"
　End If
End Sub

通勤距離申請が1.1kmのように100m単位であれば申告値から0.1を差し引きます。
例えば5kmなら4.9kmとなり4000が返えり、5.1kmなら5.0kmとなり4500を返します
また、以前のままですと2km以下の場合はエラーになりますので、If文にて2km以下のとき0を返えします。

お礼

ありがとうございます。　なるほど、考え方ですね。引くというところに頭が回りませんでした。あと少しでなんとか完成しそうですので、さっそく組み込んで使わせてもらいます。　これで、データ管理がだいぶ楽になります。

回答No.4

KURUMITO

回答Ｎｏ2です。
Ａ1セルに0を入れることができないのでしたらマクロのコードは次のようにすればよいでしょう。
Private Sub TextBox1_AfterUpdate()

Set WS1 = Worksheets("Sheet1") 'シート1に距離と金額の関係がある場合です。
If TextBox1.Text = "" Then Exit Sub
If TextBox1.Text <> "" Then
n = TextBox1.Text * 1　　　　　　　　　　’テキストのデータは文字列です。数値への変換が必要です。
If n<2 Then
TextBox2.Value=0
Else
TextBox2.Value = WorksheetFunction.VLookup(n, Range(WS1.Cells(1, 1), WS1.Cells(5, 2)), 2, True)
End If
End If
End Sub

お礼

No1の方のものと合わせて、活用させていただきます。テキストのデータを数値に変換する必要があるのですね。　ほかにもやりたいことがあるので、そちらでも参考にさせていただきます。ありがとうございました。

回答No.3

hallo-2007

別案ですが
シートに距離と金額の対応表を準備します。
仮に
　　A　　B
１
２　距離　金額
３　0　　2000
４　2.1　4000
５　5.1　4500
・・・と準備して
　A1に距離を入れると、B1に金額が表示されるように関数を準備します。
B1には　=VLOOKUP(A1,A3:B100) で大丈夫かと思います。
ユーザーフォームのVBAは
Private Sub TextBox1_Change()
Range("A1").Value=TextBox1.Value
TextBox2.Value=Range("B1").Value
End Sub
として、TextBox1の値をA1にいれて、B1の式の結果をTextBox2に引き出す。
見たいな事がわかりやすいと思います。

お礼

ありがとうございました。自分だけが使う分にはセルに関数を入れてもよいかと思ったのですが、複数にんが使うものなので、シートに関数を埋め込むと、ちょっと怖いのです。何も考えずに上書きされる可能性があるものですから・・・。

回答No.2

KURUMITO

エクセルのシートで距離と通勤手当の額の表示については、Ａ1セルを0に、Ａ2セルを２に、・・・・・、また、Ｂ1セルには０を、Ｂ2セルには2000、・・・のようにＡ1セルが0にした表であることが必要です。その後にユーザーフォームのコードについては次のようにします。
Private Sub TextBox1_AfterUpdate()

Set WS1 = Worksheets("Sheet1") 'シート1に距離と金額の関係がある場合です。
If TextBox1.Text = "" Then Exit Sub
If TextBox1.Text <> "" Then
n = TextBox1.Text * 1　　　　　　　　　　’テキストのデータは文字列です。数値への変換が必要です。
TextBox2.Value = WorksheetFunction.VLookup(n, Range(WS1.Cells(1, 1), WS1.Cells(5, 2)), 2, True)
End If
End Sub

お礼

データ用のシートには、ほかにもたくさんのデータがあるので、A1を0にできないのです。でも、他の野茂に活用させえていただきます。ありがとうございます。

回答No.1

Masa2072

難しく考えずにVLOOKUP関数を使うとよいのではないでしょうか？

たとえば
Private Sub TextBox1_Change()
　If IsNumeric(Me.TextBox1) Then
　　Me.TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), Sheet1.Range("A1:B4"), 2)
　Else
　　Me.TextBox2 = "No Numeric!"
　End If
End Sub
こんな感じで・・

お礼

私の質問の書き方が悪かったです。距離については、０～２キロは0円、2.1～4.9キロは2000円、5.0～9.9キロまでは4100円という風になるのです。申し訳ありませんでした。

補足

VLookup,でなんとか表示はできるようになりました。ありがとうございます。
　そこで、さらになんですが、動かしてみたところ、たとえば、4.6と入れると5キロ以上の金額が表示されるようになってしました。ほかの距離も同じで、たとえば10.7と入力すると15キロ以上の金額になってしまうのです。この場合は、シートの書き方なのか、それともコードの書き方で解決できるものでしょうか。