Excel VBA質問箱 IV

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

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


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

【41794】VBAで商品マスターから入力 kazuo 06/8/23(水) 2:08 質問[未読]
【41795】Re:VBAで商品マスターから入力 かみちゃん 06/8/23(水) 7:24 発言[未読]
【41805】Re:VBAで商品マスターから入力 色々な方 06/8/23(水) 11:20 発言[未読]
【41806】Re:VBAで商品マスターから入力 kazuo 06/8/23(水) 11:42 発言[未読]
【41810】Re:VBAで商品マスターから入力 かみちゃん 06/8/23(水) 14:45 回答[未読]
【41813】Re:VBAで商品マスターから入力 kazuo 06/8/23(水) 16:08 質問[未読]
【41817】Re:VBAで商品マスターから入力 かみちゃん 06/8/23(水) 17:33 回答[未読]
【41825】Re:VBAで商品マスターから入力 kazuo 06/8/23(水) 19:41 質問[未読]
【41827】Re:VBAで商品マスターから入力 かみちゃん 06/8/23(水) 20:21 回答[未読]
【41841】Re:VBAで商品マスターから入力 kazuo 06/8/23(水) 23:34 発言[未読]
【41842】Re:VBAで商品マスターから入力 かみちゃん 06/8/23(水) 23:52 回答[未読]
【41843】Re:VBAで商品マスターから入力 kazuo 06/8/23(水) 23:59 発言[未読]
【41844】Re:VBAで商品マスターから入力 かみちゃん 06/8/24(木) 0:05 発言[未読]
【41845】Re:VBAで商品マスターから入力 kazuo 06/8/24(木) 0:09 発言[未読]
【41846】Re:VBAで商品マスターから入力 kazuo 06/8/24(木) 0:14 発言[未読]
【41847】Re:VBAで商品マスターから入力 かみちゃん 06/8/24(木) 0:17 発言[未読]
【41857】Re:VBAで商品マスターから入力 kazuo 06/8/24(木) 12:26 お礼[未読]

【41794】VBAで商品マスターから入力
質問  kazuo  - 06/8/23(水) 2:08 -

引用なし
パスワード
   VBAの大先輩の皆さん よろしくお願いします。
sheet5に"商品マスター"を作成し、sheet3の"内訳明細"の
A列とB列の2つの条件が一致したら
C列とE列に呼称・単価を記入したいのです。
VLOOKUP関数ならだいたい出来るのですが
どうしても商品名が3千〜4千ありますので、VBAで作成したいのですが

sheet3内訳明細側
  A   B   C   D   E
2 品名 形状  呼称    単価入力
3 ○○ ▲▲  m      550
4 ○○ ■■  個      800
  ・
  ・

sheet5商品マスター側
  A   B     C      D      E      F
2 品名 形状     呼称   単価一般   同業単価   特別価格
3 ○○ ▲▲     m    550      450       350
4 ○○ ××     個    220      200       180
5 ○○ ■■     個    800      700       600
   ・
   3000〜4000行あります
   ・

上記表のsheet5で解るように3種類(D列・E列・F列)の単価設定の必要があります。
よって
1.最初にinputboxで単価設定(1.一般チャンネル・2.同業チャンネル・
 3.特別チャンネル)を実行したい。または(ファームのボタン選択可能にしたい)
 2通り出来れば最高です
 そのときに現在のチャンネルをsheet3"内訳明細"の"L1"に表示。
2.その後sheet3"内訳明細"の入力を開始。
3.[#41557]のcase3の条件を満たしたい。
   Case 3
    If .Value = "式" Then
     Application.EnableEvents = False
     .Offset(, 1).Value = 1
     .Offset(0, 2).Select
     Application.EnableEvents = True
    End If
4.[#41638]下記です。
  'C列に値が入ったかどうかのチェック
  With Cells(Target.Row, 3)
   If .Value <> "" Then
    .Offset(, 1).Activate
   Else
    .Activate
   End If
  End With
条件が多くてすみません。
以上です、検討の程よろしくお願いします。

【41795】Re:VBAで商品マスターから入力
発言  かみちゃん  - 06/8/23(水) 7:24 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>VBAの大先輩の皆さん

まず、はじめにこういうことを書かれると、コメントが書きにくくなります。
ここの掲示板では、先輩や後輩など上下関係はないと思います。
私を含めて、多くの方は、みんな勉強するためにコメントを書いていると思いますので・・・

>sheet5に"商品マスター"を作成し、sheet3の"内訳明細"の
>A列とB列の2つの条件が一致したら
>C列とE列に呼称・単価を記入したい

Findメソッドを使います。
まず、A列に一致するセル位置を検索し、一致したらB列と一致しているかチェックし、
一致していたら、そこで検索終了し、
一致していなかったら、次のセル位置を検索する
という考え方になります。
詳しくは、Findメソッドのヘルプの使用例にコードが載っていますので、
確認してみてください。

>どうしても商品名が3千〜4千ありますので、VBAで作成したいのですが

品名、形状の組み合わせで重複するものはないということでいいですよね?

>1.最初にinputboxで単価設定(1.一般チャンネル・2.同業チャンネル・
> 3.特別チャンネル)を実行したい。

ちょっとわからないのですが、
InputBoxで
"1"を入力したら、一般チャンネルということで「単価一般」の単価を、
Sheet5のD列から取得する
"2"を入力したら、同業チャンネルということで「同業単価」の単価を、
Sheet5のE列から取得する
"3"を入力したら、特別チャンネルということで「特別価格」の単価を、
Sheet5のF列から取得する
ということでしょうか?

> または(ファームのボタン選択可能にしたい)

ファームとは、フォームのことで、UserFormのCommandButtonやOptionButtonのようなものの
ことでしょうか?
それとも、シート上に配置するボタンのことでしょうか?
シート上に配置する場合でしたら、何かと面倒なのですが・・・

> そのときに現在のチャンネルをsheet3"内訳明細"の"L1"に表示。

L1に表示の例示がないので、よくわかりません。
L1ではなく、L列に表示したいということでしょうか?

>3.[#41557]のcase3の条件を満たしたい。

意味がわかりません。
[#41557]でのコードの機能は残したいということでしょうか?

>4.[#41638]下記です。

意味がわかりません。
[#41638]でのコードの機能は残したいということでしょうか?

>以上です、検討の程よろしくお願いします。

最後に、もうひとつ、「検討の程」というのは、kazuoさんがすることです。
その過程でわからないところがあれば、みんなで手伝いましょうというのがここの
掲示板の役割だと思います。
あくまで、仕様提示したら、コードを「検討」して提示するような、コード作成依
頼場所ではないので、その点、ご配慮いただければと思います。

今回のコメントは、質問内容の確認とFindメソッドというヒントを提示させていた
だきましたので、もう少し考えてみていただけますでしょうか?

【41805】Re:VBAで商品マスターから入力
発言  色々な方  - 06/8/23(水) 11:20 -

引用なし
パスワード
   ▼かみちゃん さん:
>こんにちは。かみちゃん です。
>
>>VBAの大先輩の皆さん
>
>まず、はじめにこういうことを書かれると、コメントが書きにくくなります。
>ここの掲示板では、先輩や後輩など上下関係はないと思います。
>最後に、もうひとつ、「検討の程」というのは、kazuoさんがすることです。
>その過程でわからないところがあれば、みんなで手伝いましょうというのがここの
>掲示板の役割だと思います。
>あくまで、仕様提示したら、コードを「検討」して提示するような、コード作成依
>頼場所ではないので、その点、ご配慮いただければと思います。

メールというものが日常のものになり様々情報が飛び交う中、
こちらの掲示板でも様々な意見が飛び交っています。
相手の性格・心情等も見えない状態で心無いコメントも見かけます。
メールでのコメントのバックグラウンドを知りもせず、
断言的な言い方は好ましくないと常に感じてみております。
例えば”大先輩”という表現は確かに好ましくないかも知れません
けれども藁をもすがりたい気持ちからそういう表現が出たのかも知れません。

>私を含めて、多くの方は、みんな勉強するためにコメントを書いていると思いますので・・・
確かに自分で考えるのは面倒なので教えてもらっちゃえの方も
いらっしゃるかも知れません。

けれども裏づけは取れないではありませんか。
一度発した発言は発したほうの記憶からは消えやすいですが、
受け止めた方はどうでしょう?

私がこうしてコメントすることも結局は同じこと。
かみちゃんさんのバックグラウンドはわかりません。
けれども他の方にも伝えたかったのです。
相手の気持ちをまず考えてからコメントをお願いしたい。

【41806】Re:VBAで商品マスターから入力
発言  kazuo  - 06/8/23(水) 11:42 -

引用なし
パスワード
   ▼かみちゃん さん:
かみちゃんおまたお世話賭けます。
>こんにちは。かみちゃん です。
>
>>VBAの大先輩の皆さん
>
>まず、はじめにこういうことを書かれると、コメントが書きにくくなります。
>ここの掲示板では、先輩や後輩など上下関係はないと思います。
>私を含めて、多くの方は、みんな勉強するためにコメントを書いていると思いますので・・・
また気分を害するようなことを書きましてすみません。
>>sheet5に"商品マスター"を作成し、sheet3の"内訳明細"の
>>A列とB列の2つの条件が一致したら
>>C列とE列に呼称・単価を記入したい
>
>Findメソッドを使います。
>まず、A列に一致するセル位置を検索し、一致したらB列と一致しているかチェックし、
>一致していたら、そこで検索終了し、
>一致していなかったら、次のセル位置を検索する
>という考え方になります。
>詳しくは、Findメソッドのヘルプの使用例にコードが載っていますので、
>確認してみてください。
イメージだけですが↓こんな感じ?・・・・・
For Each R In Range(Worksheets("商品マスター").Range("C2"), Worksheets("商品マスター").Range("C65536").End(xlUp))
  For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "内訳明細") <> 0 Then
      Set F = ws.Range("IV:IV").Find(R.Value, , xlValues, xlWhole)
      If Not F Is Nothing Then
        With Worksheets("内訳明細")
          .Range(.Cells(3), .Cells(5)).Value = _
          ws.Range(ws.Cells(F.Row, 3), ws.Cells(F.Row, 5)).Value
        End With


>
>>どうしても商品名が3千〜4千ありますので、VBAで作成したいのですが
>
>品名、形状の組み合わせで重複するものはないということでいいですよね?
はいありません。
>>1.最初にinputboxで単価設定(1.一般チャンネル・2.同業チャンネル・
>> 3.特別チャンネル)を実行したい。
>
>ちょっとわからないのですが、
>InputBoxで
>"1"を入力したら、一般チャンネルということで「単価一般」の単価を、
>Sheet5のD列から取得する
>"2"を入力したら、同業チャンネルということで「同業単価」の単価を、
>Sheet5のE列から取得する
>"3"を入力したら、特別チャンネルということで「特別価格」の単価を、
>Sheet5のF列から取得する
>ということでしょうか?
↑はいその通りです。
>> または(ファームのボタン選択可能にしたい)
>
>ファームとは、フォームのことで、UserFormのCommandButtonやOptionButtonのようなものの
>ことでしょうか?
>それとも、シート上に配置するボタンのことでしょうか?
>シート上に配置する場合でしたら、何かと面倒なのですが・・・
入力前に単価種別の設定をしなくてはなりませんのでこの書き方になりましたが
これは面倒であれば無くても結構です。
ただ下記にあるように現在どの単価で入力中なのかまた後日データを
開いたときにどの単価設定だったのか確認の為に"L1"あたり帳票の構成上に
表示しておきたいと思ってます。
>> そのときに現在のチャンネルをsheet3"内訳明細"の"L1"に表示。
>
>L1に表示の例示がないので、よくわかりません。
>L1ではなく、L列に表示したいということでしょうか?
L列でなくL1です。例としては"一般"・"同業"・"特価"です
>>3.[#41557]のcase3の条件を満たしたい。
>
>意味がわかりません。
>[#41557]でのコードの機能は残したいということでしょうか?
はい同機能は残したいと言うことです。
>>4.[#41638]下記です。
>
>意味がわかりません。
>[#41638]でのコードの機能は残したいということでしょうか?
はい同機能は残したいと言うことです。
>
>>以上です、検討の程よろしくお願いします。
>
>最後に、もうひとつ、「検討の程」というのは、kazuoさんがすることです。
>その過程でわからないところがあれば、みんなで手伝いましょうというのがここの
>掲示板の役割だと思います。
>あくまで、仕様提示したら、コードを「検討」して提示するような、コード作成依
>頼場所ではないので、その点、ご配慮いただければと思います。
書き方が変ですね考えるのは私の方なのですが、なにせ技術力が伴わないもので
こういう表現になってしまいました。何とぞよろしくお願いします。
>今回のコメントは、質問内容の確認とFindメソッドというヒントを提示させていた
>だきましたので、もう少し考えてみていただけますでしょうか?
過去ログ・ヘルプをFindがどういうものなのかは理解しましたが
即コードの実現までは及びません。お知恵をおかしくだされ。m(_ _)m
上記の説明で仕様?はわかりますか。・・・

【41810】Re:VBAで商品マスターから入力
回答  かみちゃん E-MAIL  - 06/8/23(水) 14:45 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>>>VBAの大先輩の皆さん
>>
>>まず、はじめにこういうことを書かれると、コメントが書きにくくなります。
>>ここの掲示板では、先輩や後輩など上下関係はないと思います。
>>私を含めて、多くの方は、みんな勉強するためにコメントを書いていると思いますので・・・
>また気分を害するようなことを書きましてすみません。

別に気分を害したとかでは全然なく、むしろ、そういうこと書かれると「大先輩ではない」人が書きにくくなって、もらえるコメントももらいづらくなりますよという意味でした。
まぁ、私も、変なところに拘らなくてもいいわけですし、藁をもつかみたい気持ちは、よ〜くわかっているつもりですので。

>>>sheet5に"商品マスター"を作成し、sheet3の"内訳明細"の
>>>A列とB列の2つの条件が一致したら
>>>C列とE列に呼称・単価を記入したい
>>
>>Findメソッドを使います。
>>まず、A列に一致するセル位置を検索し、一致したらB列と一致しているかチェックし、
>>一致していたら、そこで検索終了し、
>>一致していなかったら、次のセル位置を検索する
>>という考え方になります。
>>詳しくは、Findメソッドのヘルプの使用例にコードが載っていますので、
>>確認してみてください。
>イメージだけですが↓こんな感じ?・・・・・

内訳明細シートが複数あるのですか?
初めて聞いたような気がします。

B列に入力があれば、単価の検索ができるように修正してみました。
[#41501]および[#41634]で解決したコードに加えてみると、以下のような感じになります。
動作確認していただけるよう全体のコードを再度提示させていただきます。
現在、お手元のコードと異なるところがあれば、ご指摘ください。

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim hinmei As String, keijyou As String
 Dim myRange As Range
 Dim endRow As Long
 Dim a As Variant
 Dim i As Variant
  
 Dim c As Range '★
 Dim FirstAddress As String '★
 Dim rngFind As Range '★
 Dim blnDataSet As Boolean '★

 With Target
'  On Error GoTo errEnd
  If .Column <= 1 Or .Column >= 4 Or _
    .Row = 1 Then End

  Select Case .Column
'   Case 1
'    If .Offset(, 1).Value = "" Then Exit Sub
'     hinmei = .Value
'     keijyou = .Offset(, 1).Value
'     GoTo kakuninEvent
   Case 2
    If .Offset(, -1).Value = "" Then Exit Sub
     hinmei = .Offset(, -1).Value
     keijyou = .Value
     GoTo kakuninEvent
   Case 3
    If .Value = "式" Then
     Application.EnableEvents = False
     .Offset(, 1).Value = 1
     .Offset(0, 2).Select
     Application.EnableEvents = True
    End If
  End Select

  Exit Sub

kakuninEvent:
  Set myRange = Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp).Offset(-1)).Resize(, 5)
  a = myRange.Value
    Application.EnableEvents = False
    Range("C" & .Row).ClearContents
    Range("E" & .Row).ClearContents
    Application.EnableEvents = True
  For i = 1 To myRange.Rows.Count
   If hinmei = a(i, 1) And keijyou = a(i, 2) Then
    Application.EnableEvents = False
    Range("C" & .Row).Value = a(i, 3)
    Range("E" & .Row).Value = a(i, 5)
    Application.EnableEvents = True
    Exit For
   End If
  Next i
  '単価を検索し設定 '★
  Set rngFind = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A65536").End(xlUp))
  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
  blnDataSet = False
  If Not c Is Nothing Then
   FirstAddress = c.Address
   Do
    If .Value = c.Offset(, 1).Value Then
     blnDataSet = True
     Application.EnableEvents = False
     Select Case Range("L1").Value
      Case "一般"
       .Offset(, 3).Value = c.Offset(, 3).Value
      Case "同業"
       .Offset(, 3).Value = c.Offset(, 4).Value
      Case "特別"
       .Offset(, 3).Value = c.Offset(, 5).Value
      Case Else
       MsgBox "単価種別が違います [" & Range("L1").Value & "]"
     End Select
     Application.EnableEvents = True
     Exit Do
    End If
    Set c = rngFind.FindNext(c)
   Loop While Not c Is Nothing And c.Address <> FirstAddress
  End If
  If Not blnDataSet Then
   MsgBox "単価が見つかりません"
  End If
  '----ここまで単価検索 '★
  
  'C列に値が入ったかどうかのチェック
  With Cells(Target.Row, 3)
   If .Value <> "" Then
    .Offset(, 1).Activate
   Else
    .Activate
   End If
  End With
 End With
'errEnd:

End Sub

>>>1.最初にinputboxで単価設定(1.一般チャンネル・2.同業チャンネル・
>>> 3.特別チャンネル)を実行したい。
>>
>>ちょっとわからないのですが、
>>InputBoxで
>>"1"を入力したら、一般チャンネルということで「単価一般」の単価を、
>>Sheet5のD列から取得する
>>"2"を入力したら、同業チャンネルということで「同業単価」の単価を、
>>Sheet5のE列から取得する
>>"3"を入力したら、特別チャンネルということで「特別価格」の単価を、
>>Sheet5のF列から取得する
>>ということでしょうか?

今回は、InputBoxや、UserFormを使わずに、
L1セルに「一般」「同業」「特別」という文字列を入力しておくことによって分岐するようにしてあります。
これで動作確認ができれば、
InputBoxや、UserFormの条件でL1セルを書き換えることだけを考えればいいことになります。

【41813】Re:VBAで商品マスターから入力
質問  kazuo  - 06/8/23(水) 16:08 -

引用なし
パスワード
   ▼かみちゃん さん:
>こんにちは。かみちゃん です。
>
>>>>VBAの大先輩の皆さん
>>>
>>>まず、はじめにこういうことを書かれると、コメントが書きにくくなります。
>>>ここの掲示板では、先輩や後輩など上下関係はないと思います。
>>>私を含めて、多くの方は、みんな勉強するためにコメントを書いていると思いますので・・・
>>また気分を害するようなことを書きましてすみません。
>
>別に気分を害したとかでは全然なく、むしろ、そういうこと書かれると「大先輩ではない」人が書きにくくなって、もらえるコメントももらいづらくなりますよという意味でした。
>まぁ、私も、変なところに拘らなくてもいいわけですし、藁をもつかみたい気持ちは、よ〜くわかっているつもりですので。

敬意を表する意味合いで書いているのですが。
あまり気になさらないでください。

>>>>sheet5に"商品マスター"を作成し、sheet3の"内訳明細"の
>>>>A列とB列の2つの条件が一致したら
>>>>C列とE列に呼称・単価を記入したい
>>>
>>>Findメソッドを使います。
>>>まず、A列に一致するセル位置を検索し、一致したらB列と一致しているかチェックし、
>>>一致していたら、そこで検索終了し、
>>>一致していなかったら、次のセル位置を検索する
>>>という考え方になります。
>>>詳しくは、Findメソッドのヘルプの使用例にコードが載っていますので、
>>>確認してみてください。
>>イメージだけですが↓こんな感じ?・・・・・
>
>内訳明細シートが複数あるのですか?
>初めて聞いたような気がします。
いえいえ内訳明細は1シートのみです。
そんな書込ありましたか?
>B列に入力があれば、単価の検索ができるように修正してみました。
>[#41501]および[#41634]で解決したコードに加えてみると、以下のような感じになります。
>動作確認していただけるよう全体のコードを再度提示させていただきます。
>現在、お手元のコードと異なるところがあれば、ご指摘ください。
>
>Private Sub Worksheet_Change(ByVal Target As Range)
> Dim hinmei As String, keijyou As String
> Dim myRange As Range
> Dim endRow As Long
> Dim a As Variant
> Dim i As Variant
>  
> Dim c As Range '★
> Dim FirstAddress As String '★
> Dim rngFind As Range '★
> Dim blnDataSet As Boolean '★
>
> With Target
>'  On Error GoTo errEnd
>  If .Column <= 1 Or .Column >= 4 Or _
>    .Row = 1 Then End
>
>  Select Case .Column
>'   Case 1
>'    If .Offset(, 1).Value = "" Then Exit Sub
>'     hinmei = .Value
>'     keijyou = .Offset(, 1).Value
>'     GoTo kakuninEvent
>   Case 2
>    If .Offset(, -1).Value = "" Then Exit Sub
>     hinmei = .Offset(, -1).Value
>     keijyou = .Value
>     GoTo kakuninEvent
>   Case 3
>    If .Value = "式" Then
>     Application.EnableEvents = False
>     .Offset(, 1).Value = 1
>     .Offset(0, 2).Select
>     Application.EnableEvents = True
>    End If
>  End Select
>
>  Exit Sub
>
>kakuninEvent:
>  Set myRange = Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp).Offset(-1)).Resize(, 5)
>  a = myRange.Value
>    Application.EnableEvents = False
>    Range("C" & .Row).ClearContents
>    Range("E" & .Row).ClearContents
>    Application.EnableEvents = True
>  For i = 1 To myRange.Rows.Count
>   If hinmei = a(i, 1) And keijyou = a(i, 2) Then
>    Application.EnableEvents = False
>    Range("C" & .Row).Value = a(i, 3)
>    Range("E" & .Row).Value = a(i, 5)
>    Application.EnableEvents = True
>    Exit For
>   End If
>  Next i
>  '単価を検索し設定 '★
>  Set rngFind = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A65536").End(xlUp))
>  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
>  blnDataSet = False
>  If Not c Is Nothing Then
>   FirstAddress = c.Address
>   Do
>    If .Value = c.Offset(, 1).Value Then
>     blnDataSet = True
>     Application.EnableEvents = False
>     Select Case Range("L1").Value
>      Case "一般"
>       .Offset(, 3).Value = c.Offset(, 3).Value
>      Case "同業"
>       .Offset(, 3).Value = c.Offset(, 4).Value
>      Case "特別"
>       .Offset(, 3).Value = c.Offset(, 5).Value
>      Case Else
>       MsgBox "単価種別が違います [" & Range("L1").Value & "]"
>     End Select
>     Application.EnableEvents = True
>     Exit Do
>    End If
>    Set c = rngFind.FindNext(c)
>   Loop While Not c Is Nothing And c.Address <> FirstAddress
>  End If
>  If Not blnDataSet Then
>   MsgBox "単価が見つかりません"
>  End If
>  '----ここまで単価検索 '★
>  
>  'C列に値が入ったかどうかのチェック
>  With Cells(Target.Row, 3)
>   If .Value <> "" Then
>    .Offset(, 1).Activate
>   Else
>    .Activate
>   End If
>  End With
> End With
>'errEnd:
>
>End Sub
コード動作確認の結果をお伝えいたします。
まず
>  Set rngFind = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A65536").End(xlUp))
ここで実行時エラー9でインデックスが有効範囲にありませんと出ました。
これについてはsheet2の部分を"商品マスター"に変更し解決しました。
次に"L1"に"一般"と書込帳票作成実施・結果はE列の単価のみ表示されます
sheet5の商品マスターD列の値です。
(C列は表示されずです呼称の部分です)
相談ですが機能的に前の仕様が原因で問題(C列に入らない)だとすれば
機能を残していただきたいのは単純に"式"と入れたらセル1つ飛びと
一番最後のコードの部分↓ココだけでよいのです
  'C列に値が入ったかどうかのチェック
  With Cells(Target.Row, 3)
   If .Value <> "" Then
    .Offset(, 1).Activate
   Else
    .Activate
   End If
  End With
 End With
★これだけが機能として残したいだけです。
追記:やはり前の仕様が原因ではないかと思う動作をします。
   一度入力済みのA,B列記入したものは"m"or"個"など表示されます
   A,B列が新規入力の場合C列"m"等入力されません。
   呼称についてもsheet5商品マスターから引っ張ってくる方がよいかと
   思いますので、いかがでしょうか。

>>>>1.最初にinputboxで単価設定(1.一般チャンネル・2.同業チャンネル・
>>>> 3.特別チャンネル)を実行したい。
>>>
>>>ちょっとわからないのですが、
>>>InputBoxで
>>>"1"を入力したら、一般チャンネルということで「単価一般」の単価を、
>>>Sheet5のD列から取得する
>>>"2"を入力したら、同業チャンネルということで「同業単価」の単価を、
>>>Sheet5のE列から取得する
>>>"3"を入力したら、特別チャンネルということで「特別価格」の単価を、
>>>Sheet5のF列から取得する
>>>ということでしょうか?
>
>今回は、InputBoxや、UserFormを使わずに、
>L1セルに「一般」「同業」「特別」という文字列を入力しておくことによって分岐するようにしてあります。
>これで動作確認ができれば、
>InputBoxや、UserFormの条件でL1セルを書き換えることだけを考えればいいことになります。
上記説明は了解しました動作確認が先ですね。(-_-;)

【41817】Re:VBAで商品マスターから入力
回答  かみちゃん E-MAIL  - 06/8/23(水) 17:33 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>>>>>VBAの大先輩の皆さん
>
>敬意を表する意味合いで書いているのですが。
>あまり気になさらないでください。

長く引っ張るつもりは、ありませんが、ここの掲示板については、そのようなご配慮は特に必要ないと思います。
先輩とか後輩では、なく、助け合いですから。。。そういう敬意があると、かえって、構えてしまいます。
まぁ、この部分は、このあたりに終わりにしましょう。

>>内訳明細シートが複数あるのですか?
>>初めて聞いたような気がします。
>いえいえ内訳明細は1シートのみです。
>そんな書込ありましたか?

kazuoさんご自身が提示された以下のコードの意味をご理解されているのでしょうか?
 For Each ws In ThisWorkbook.Worksheets
  If InStr(1, ws.Name, "内訳明細") <> 0 Then
このコードの記述から想定して、おたずねしました。

>コード動作確認の結果をお伝えいたします。
>まず
>>  Set rngFind = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A65536").End(xlUp))
>ここで実行時エラー9でインデックスが有効範囲にありませんと出ました。
>これについてはsheet2の部分を"商品マスター"に変更し解決しました。

申し訳ありません。前提条件の提示がもれていました。
Sheet2は、Sheet5のことです。
Sheet5が本当は「商品マスター」というシート名なら、それで「商品マスター」にしてください。

>次に"L1"に"一般"と書込帳票作成実施・結果はE列の単価のみ表示されます
>sheet5の商品マスターD列の値です。
>(C列は表示されずです呼称の部分です)
>相談ですが機能的に前の仕様が原因で問題(C列に入らない)だとすれば
>機能を残していただきたいのは単純に"式"と入れたらセル1つ飛びと
>一番最後のコードの部分↓ココだけでよいのです

申し訳ありません。
> C列とE列に呼称・単価を記入したいのです。
この仕様を見落としていて、E列の単価の判断のみにばかり気にしていました。
ただ、単価がきちんと反映できているのであれば、それがどこでできているのか・・・・

>     Select Case Range("L1").Value
>      Case "一般"
>       .Offset(, 3).Value = c.Offset(, 3).Value
>      Case "同業"
>       .Offset(, 3).Value = c.Offset(, 4).Value
>      Case "特別"
>       .Offset(, 3).Value = c.Offset(, 5).Value
>      Case Else
>       MsgBox "単価種別が違います [" & Range("L1").Value & "]"
>     End Select

という部分で行っているということはおわかりいただけましたでしょうか?
単価の設定は、
.Offset(, 3).Value = c.Offset(, 3).Value
のコードですから、
呼称もということでしたら、
.Offset(, 1).Value = c.Offset(, 2).Value
も追加するといいということになります。
ここが応用になります。

>追記:やはり前の仕様が原因ではないかと思う動作をします。
>   一度入力済みのA,B列記入したものは"m"or"個"など表示されます
>   A,B列が新規入力の場合C列"m"等入力されません。
>   呼称についてもsheet5商品マスターから引っ張ってくる方がよいかと
>   思いますので、いかがでしょうか。

前の仕様が原因というわけではないので、そのままにしておきます。
前の仕様のうち、一度入力済みのものは、「商品マスター」から参照してくるのではなく、入力済みの値を常に参照するようにするということでいいですよね?
その仕様であれば、以下のよなコードに修正してください。
◆部分が修正箇所です。

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim hinmei As String, keijyou As String
 Dim myRange As Range
 Dim endRow As Long
 Dim a As Variant
 Dim i As Variant
  
 Dim c As Range '★
 Dim FirstAddress As String '★
 Dim rngFind As Range '★
 Dim blnDataSet As Boolean '★

 With Target
'  On Error GoTo errEnd
  If .Column <= 1 Or .Column >= 4 Or _
    .Row = 1 Then End

  Select Case .Column
'   Case 1
'    If .Offset(, 1).Value = "" Then Exit Sub
'     hinmei = .Value
'     keijyou = .Offset(, 1).Value
'     GoTo kakuninEvent
   Case 2
    If .Offset(, -1).Value = "" Then Exit Sub
     hinmei = .Offset(, -1).Value
     keijyou = .Value
     GoTo kakuninEvent
   Case 3
    If .Value = "式" Then
     Application.EnableEvents = False
     .Offset(, 1).Value = 1
     .Offset(0, 2).Select
     Application.EnableEvents = True
    End If
  End Select

  Exit Sub

kakuninEvent:
  Set myRange = Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp).Offset(-1)).Resize(, 5)
  a = myRange.Value
    Application.EnableEvents = False
    Range("C" & .Row).ClearContents
    Range("E" & .Row).ClearContents
    Application.EnableEvents = True
  For i = 1 To myRange.Rows.Count
   If hinmei = a(i, 1) And keijyou = a(i, 2) Then
    Application.EnableEvents = False
    Range("C" & .Row).Value = a(i, 3)
    Range("E" & .Row).Value = a(i, 5)
    Application.EnableEvents = True
    Exit For
   End If
  Next i
  If .Value <> "" And Range("C" & .Row).Value = "" And Range("E" & .Row).Value = "" Then '◆
  '単価を検索し設定 '★
  Set rngFind = Sheets("商品マスター").Range("A1", Sheets("商品マスター").Range("A65536").End(xlUp))
  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
  blnDataSet = False
  If Not c Is Nothing Then
   FirstAddress = c.Address
   Do
    If .Value = c.Offset(, 1).Value Then
     blnDataSet = True
     Application.EnableEvents = False
     Select Case Range("L1").Value
      Case "一般"
       .Offset(, 1).Value = c.Offset(, 2).Value '◆
       .Offset(, 3).Value = c.Offset(, 3).Value
      Case "同業"
       .Offset(, 1).Value = c.Offset(, 2).Value '◆
       .Offset(, 3).Value = c.Offset(, 4).Value
      Case "特別"
       .Offset(, 1).Value = c.Offset(, 2).Value '◆
       .Offset(, 3).Value = c.Offset(, 5).Value
      Case Else
       MsgBox "単価種別が違います [" & Range("L1").Value & "]"
     End Select
     Application.EnableEvents = True
     Exit Do
    End If
    Set c = rngFind.FindNext(c)
   Loop While Not c Is Nothing And c.Address <> FirstAddress
  End If
  If Not blnDataSet Then
   MsgBox "単価が見つかりません"
  End If
  End If '◆
  '----ここまで単価検索 '★
  
  'C列に値が入ったかどうかのチェック
  With Cells(Target.Row, 3)
   If .Value <> "" Then
    .Offset(, 1).Activate
   Else
    .Activate
   End If
  End With
 End With
'errEnd:

End Sub

【41825】Re:VBAで商品マスターから入力
質問  kazuo  - 06/8/23(水) 19:41 -

引用なし
パスワード
   ▼かみちゃん さん:
いつも素早い対応ありがとうございます。
確認作業に手間取り動作報告が遅くなりました。
すこぶる軽快・快調です。
全て問題なく動作してます。

> For Each ws In ThisWorkbook.Worksheets
>  If InStr(1, ws.Name, "内訳明細") <> 0 Then
上記はログ参考に探したもので意味が全てわかってませんでした。
内訳明細シートは1シートです。

"L1"の設定ですが下記のように過去ログ参考に作成してみたのですが
Sub Main()
Dim X As Long

  Rtn = InputBox("1:一般 2:同業 3:特別のいずれかを入力してください")
  X = Val(Rtn)
  Select Case X
    Case 1
     Range("L1").Value = "一般"
    Case 2
     Range("L1").Value = "同業"
    Case 3
     Range("L1").Value = "特別"
  End Select
End Sub
かみちゃんにチェック頂きたいのともっといい方法があれば
教えてください。

>.Offset(, 3).Value = c.Offset(, 3).Value
>のコードですから、
>呼称もということでしたら、
>.Offset(, 1).Value = c.Offset(, 2).Value
>も追加するといいということになります。
>ここが応用になります。
上記の構文.Offset(, 1).Value = c.Offset(, 2).Valueは、
C列を基準に1列目2列目を読みに行くと言うことですか?

>前の仕様が原因というわけではないので、そのままにしておきます。
>前の仕様のうち、一度入力済みのものは、「商品マスター」から参照してくるので>はなく、入力済みの値を常に参照するようにするということでいいですよね?
上記仮に"商品マスター"のみ参照がいいなとなった場合
何処を削除すれば宜しいでしょうか。(セル移動のコードは残しますが)
前の仕様もいい場合もあるのですが内容により使い分けをしたいと思います。

最後にもう一つあるのですが
>MsgBox "単価種別が違います [" & Range("L1").Value & "]"
この条件とはどういう状態の時にでますか。
"単価が見つかりません"は"商品マスター"にデータがないときでましたが?
いくつもの質問ですがお付き合い下さい。
よろしくお願いします。

【41827】Re:VBAで商品マスターから入力
回答  かみちゃん E-MAIL  - 06/8/23(水) 20:21 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>"L1"の設定ですが下記のように過去ログ参考に作成してみたのですが
>Sub Main()
>Dim X As Long
>
>  Rtn = InputBox("1:一般 2:同業 3:特別のいずれかを入力してください")
>  X = Val(Rtn)
>  Select Case X
>    Case 1
>     Range("L1").Value = "一般"
>    Case 2
>     Range("L1").Value = "同業"
>    Case 3
>     Range("L1").Value = "特別"
>  End Select
>End Sub
>かみちゃんにチェック頂きたいのともっといい方法があれば
>教えてください。

私なら、以下のようにします。
ポイントは、
   Range("A1").Value = Choose(Val(Rtn), "一般", "同業", "特別")
の部分と、
入力値が間違うと再入力を促すようにするエラーチェックをしています。
また、
変数Rtnの宣言もきちんとしておきます。

Sub Main2()
 Dim Rtn As String
 
 Do
  Rtn = InputBox("1:一般 2:同業 3:特別のいずれかを入力してください")
  If Rtn = "" Then Exit Sub
  If Rtn >= "1" And Rtn <= "3" Then
   Range("A1").Value = Choose(Val(Rtn), "一般", "同業", "特別")
   Exit Do
  Else
   MsgBox "単価種別を入力しなおしてください。"
  End If
 Loop
End Sub

>>.Offset(, 3).Value = c.Offset(, 3).Value
>>のコードですから、
>>呼称もということでしたら、
>>.Offset(, 1).Value = c.Offset(, 2).Value
>>も追加するといいということになります。
>>ここが応用になります。
>上記の構文.Offset(, 1).Value = c.Offset(, 2).Valueは、
>C列を基準に1列目2列目を読みに行くと言うことですか?

違います。
.Offset(, 1).Value = c.Offset(, 2).Value
の左辺側である
.Offset(, 1).Value
は、
With Target
 .Offset(, 1).Value
ですので、今回の場合、
B列の入力が行なわれたら、1列右隣のC列を右辺の値とするという意味です。

一方、右辺側の
c.Offset(, 2).Value
は、
商品マスターのA列(ちょっとわかりづらいかもしれませんが)を検索して見つか
ったセルの2列右隣のC列の値ということになります。

>
>>前の仕様が原因というわけではないので、そのままにしておきます。
>>前の仕様のうち、一度入力済みのものは、「商品マスター」から参照してくるので>はなく、入力済みの値を常に参照するようにするということでいいですよね?
>上記仮に"商品マスター"のみ参照がいいなとなった場合
>何処を削除すれば宜しいでしょうか。(セル移動のコードは残しますが)
>前の仕様もいい場合もあるのですが内容により使い分けをしたいと思います。

どのような使い分けをなさるおつもりかわかりませんが、
一応、すべてを商品マスターから取得するのであれば、
以下の部分は、不要です。

  Set myRange = Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp).Offset(-1)).Resize(, 5)
  a = myRange.Value
    Application.EnableEvents = False
    Range("C" & .Row).ClearContents
    Range("E" & .Row).ClearContents
    Application.EnableEvents = True
  For i = 1 To myRange.Rows.Count
   If hinmei = a(i, 1) And keijyou = a(i, 2) Then
    Application.EnableEvents = False
    Range("C" & .Row).Value = a(i, 3)
    Range("E" & .Row).Value = a(i, 5)
    Application.EnableEvents = True
    Exit For
   End If
  Next i

また、上のコードを削除すると
If .Value <> "" And Range("C" & .Row).Value = "" And Range("E" & .Row).Value = "" Then '◆

End If '◆
の2行も意味のないコードになりますので、不要です。

>最後にもう一つあるのですが
>>MsgBox "単価種別が違います [" & Range("L1").Value & "]"
>この条件とはどういう状態の時にでますか。

L1の値が「一般」「同業」「特別」のいずれでもないときです。
そういうことは絶対ないとはいえないですよね?
InputBoxで、「一般」「同業」「特別」以外の文字を入れたらどうなるでしょうか?
また、キャンセルしたらどうなるでしょうか?
言い始めるとキリがありません。。。

【41841】Re:VBAで商品マスターから入力
発言  kazuo  - 06/8/23(水) 23:34 -

引用なし
パスワード
   ▼かみちゃん さん:
仕事で出かけてまして確認が遅くなりました。
早急に最終動作確認させていただきます。
その前に最終行に書きました全角・半角入力の
方法はありませんか?
>こんにちは。かみちゃん です。

>>かみちゃんにチェック頂きたいのともっといい方法があれば
>>教えてください。
>
>私なら、以下のようにします。
>ポイントは、
>   Range("A1").Value = Choose(Val(Rtn), "一般", "同業", "特別")
>の部分と、
>入力値が間違うと再入力を促すようにするエラーチェックをしています。
>また、
>変数Rtnの宣言もきちんとしておきます。
>
>Sub Main2()
> Dim Rtn As String
> 
> Do
>  Rtn = InputBox("1:一般 2:同業 3:特別のいずれかを入力してください")
>  If Rtn = "" Then Exit Sub
>  If Rtn >= "1" And Rtn <= "3" Then
>   Range("A1").Value = Choose(Val(Rtn), "一般", "同業", "特別")
>   Exit Do
>  Else
>   MsgBox "単価種別を入力しなおしてください。"
>  End If
> Loop
>End Sub
うぅ〜なるほど・・・
やはりポイントが違いますね。
>違います。
>.Offset(, 1).Value = c.Offset(, 2).Value
>の左辺側である
>.Offset(, 1).Value
>は、
>With Target
> .Offset(, 1).Value
>ですので、今回の場合、
>B列の入力が行なわれたら、1列右隣のC列を右辺の値とするという意味です。
>
>一方、右辺側の
>c.Offset(, 2).Value
>は、
>商品マスターのA列(ちょっとわかりづらいかもしれませんが)を検索して見つか
>ったセルの2列右隣のC列の値ということになります。
やはりむずかしいですね

>
>どのような使い分けをなさるおつもりかわかりませんが、
>一応、すべてを商品マスターから取得するのであれば、
>以下の部分は、不要です。
>
>  Set myRange = Range("A2", Cells(Cells.Rows.Count, 1).End(xlUp).Offset(-1)).Resize(, 5)
>  a = myRange.Value
>    Application.EnableEvents = False
>    Range("C" & .Row).ClearContents
>    Range("E" & .Row).ClearContents
>    Application.EnableEvents = True
>  For i = 1 To myRange.Rows.Count
>   If hinmei = a(i, 1) And keijyou = a(i, 2) Then
>    Application.EnableEvents = False
>    Range("C" & .Row).Value = a(i, 3)
>    Range("E" & .Row).Value = a(i, 5)
>    Application.EnableEvents = True
>    Exit For
>   End If
>  Next i
>
>また、上のコードを削除すると
>If .Value <> "" And Range("C" & .Row).Value = "" And Range("E" & .Row).Value = "" Then '◆
>と
>End If '◆
>の2行も意味のないコードになりますので、不要です。
>
>>最後にもう一つあるのですが
>>>MsgBox "単価種別が違います [" & Range("L1").Value & "]"
>>この条件とはどういう状態の時にでますか。
>
>L1の値が「一般」「同業」「特別」のいずれでもないときです。
>そういうことは絶対ないとはいえないですよね?
そうですねないとは言い切れません
>InputBoxで、「一般」「同業」「特別」以外の文字を入れたらどうなるでしょうか?
3択で行きます。また状況が変わったら相談させてください。
>また、キャンセルしたらどうなるでしょうか?
>言い始めるとキリがありません。。。
商品マスタなのですが(A,B列) 全角・半角(カタカナ・英字・数字)
は条件一致として見なすこと出来ないでしょうか。

【41842】Re:VBAで商品マスターから入力
回答  かみちゃん E-MAIL  - 06/8/23(水) 23:52 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>商品マスタなのですが(A,B列) 全角・半角(カタカナ・英字・数字)
>は条件一致として見なすこと出来ないでしょうか。

Findメソッドのヘルプを確認していただきたいのですが、
引数MatchByteに「半角角と全角を区別するには、True を指定します。」

具体的には、以下のような感じです。
  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchByte:=True)

【41843】Re:VBAで商品マスターから入力
発言  kazuo  - 06/8/23(水) 23:59 -

引用なし
パスワード
   ▼かみちゃん さん:
>こんにちは。かみちゃん です。
>
>>商品マスタなのですが(A,B列) 全角・半角(カタカナ・英字・数字)
>>は条件一致として見なすこと出来ないでしょうか。
>
>Findメソッドのヘルプを確認していただきたいのですが、
>引数MatchByteに「半角角と全角を区別するには、True を指定します。」
>
>具体的には、以下のような感じです。
>  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchByte:=True)
はいわかりました。ありがとうございます。
それと下記部分"A1"は"L1"でいいんですよね
Sub Main2()
 Dim Rtn As String
 
 Do
  Rtn = InputBox("1:一般 2:同業 3:特別のいずれかを入力してください")
  If Rtn = "" Then Exit Sub
  If Rtn >= "1" And Rtn <= "3" Then
   Range("A1").Value = Choose(Val(Rtn), "一般", "同業", "特別")
     ~~~~~~~~~~~
   Exit Do
  Else
   MsgBox "単価種別を入力しなおしてください。"
  End If
 Loop
End Sub

【41844】Re:VBAで商品マスターから入力
発言  かみちゃん E-MAIL  - 06/8/24(木) 0:05 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>それと下記部分"A1"は"L1"でいいんですよね

ごめんなさい。そうです。
あくまでサンプルです。
動作確認できたら、適宜変更して使ってください。

【41845】Re:VBAで商品マスターから入力
発言  kazuo  - 06/8/24(木) 0:09 -

引用なし
パスワード
   ▼かみちゃん さん:
>こんにちは。かみちゃん です。
>
>>商品マスタなのですが(A,B列) 全角・半角(カタカナ・英字・数字)
>>は条件一致として見なすこと出来ないでしょうか。
>
>Findメソッドのヘルプを確認していただきたいのですが、
>引数MatchByteに「半角角と全角を区別するには、True を指定します。」
>
>具体的には、以下のような感じです。
>  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchByte:=True)
上記コードに追加したのですが
商品マスター側が半角たとえば"カルビ丼"とあり"カルビ丼"と入力したのですが
C列に単価が見つかりませんが出てしまいます

【41846】Re:VBAで商品マスターから入力
発言  kazuo  - 06/8/24(木) 0:14 -

引用なし
パスワード
   ▼kazuo さん:
>▼かみちゃん さん:
>>こんにちは。かみちゃん です。
>>
>>>商品マスタなのですが(A,B列) 全角・半角(カタカナ・英字・数字)
>>>は条件一致として見なすこと出来ないでしょうか。
>>
>>Findメソッドのヘルプを確認していただきたいのですが、
>>引数MatchByteに「半角角と全角を区別するには、True を指定します。」
>>
>>具体的には、以下のような感じです。
>>  Set c = rngFind.Find(Target.Offset(, -1).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchByte:=True)
>上記コードに追加したのですが
>商品マスター側が半角たとえば"カルビ丼"とあり"カルビ丼"と入力したのですが
>C列に単価が見つかりませんが出てしまいます
ヘルプ見ました Falseですね

【41847】Re:VBAで商品マスターから入力
発言  かみちゃん E-MAIL  - 06/8/24(木) 0:17 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>>商品マスター側が半角たとえば"カルビ丼"とあり"カルビ丼"と入力したのですが
>>C列に単価が見つかりませんが出てしまいます
>ヘルプ見ました Falseですね

ヘルプでご確認いただいたとおり、全角・半角は無視して同一かどうかを判断す
るのであれば、Falseにします。

【41857】Re:VBAで商品マスターから入力
お礼  kazuo  - 06/8/24(木) 12:26 -

引用なし
パスワード
   ▼かみちゃん さん:
かみちゃん全動作チェック完了しました。
全てうまくいきました
長い時間本当にありがとうございました。
また名前みかけたら指導お願いします。(^_^)

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