Excel VBA質問箱 IV

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

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


4230 / 13646 ツリー ←次へ | 前へ→

【57576】検索して分岐 M 08/9/3(水) 12:06 質問[未読]
【57577】Re:検索して分岐 ハチ 08/9/3(水) 12:42 発言[未読]
【57580】Re:検索して分岐 M 08/9/3(水) 13:07 回答[未読]
【57579】Re:検索して分岐 kanabun 08/9/3(水) 13:04 発言[未読]
【57582】Re:検索して分岐 kanabun 08/9/3(水) 13:50 発言[未読]
【57585】Re:検索して分岐 M 08/9/3(水) 14:25 回答[未読]
【57586】Re:検索して分岐 ハチ 08/9/3(水) 15:03 発言[未読]
【57595】Re:検索して分岐 M 08/9/3(水) 21:05 お礼[未読]
【57596】Re:検索して分岐 kanabun 08/9/3(水) 21:39 発言[未読]
【57598】Re:検索して分岐 kanabun 08/9/3(水) 22:19 発言[未読]
【57603】Re:検索して分岐 M 08/9/4(木) 8:07 お礼[未読]
【57686】Re:検索して分岐 M 08/9/9(火) 17:25 質問[未読]
【57687】Re:検索して分岐 kanabun 08/9/9(火) 19:52 質問[未読]
【57689】Re:検索して分岐 M 08/9/9(火) 20:42 お礼[未読]
【57688】Re:検索して分岐 kanabun 08/9/9(火) 20:34 発言[未読]
【57690】Re:検索して分岐 M 08/9/9(火) 20:55 お礼[未読]
【57697】Re:検索して分岐 M 08/9/10(水) 8:20 発言[未読]
【57698】Re:検索して分岐 ハチ 08/9/10(水) 8:51 発言[未読]
【57704】Re:検索して分岐 M 08/9/10(水) 10:31 発言[未読]
【57705】Re:検索して分岐 ハチ 08/9/10(水) 10:42 発言[未読]
【57721】【すいません】Re:検索して分岐 ハチ 08/9/10(水) 15:16 発言[未読]
【57722】Re:【すいません】Re:検索して分岐 kanabun 08/9/10(水) 15:28 発言[未読]
【57726】Re:【すいません】Re:検索して分岐 M 08/9/10(水) 17:14 お礼[未読]
【57735】Re:検索して分岐 kanabun 08/9/11(木) 9:12 発言[未読]
【57741】Re:検索して分岐 M 08/9/11(木) 18:15 お礼[未読]
【57742】Re:検索して分岐 kanabun 08/9/11(木) 19:25 発言[未読]
【57751】Re:検索して分岐 M 08/9/12(金) 7:18 発言[未読]
【57752】Re:検索して分岐 kanabun 08/9/12(金) 8:57 発言[未読]
【57787】Re:検索して分岐 M 08/9/13(土) 12:40 お礼[未読]
【57713】Re:検索して分岐 kanabun 08/9/10(水) 12:50 発言[未読]
【57715】Re:検索して分岐 M 08/9/10(水) 14:06 発言[未読]
【57716】Re:検索して分岐 M 08/9/10(水) 14:09 発言[未読]

【57576】検索して分岐
質問  M  - 08/9/3(水) 12:06 -

引用なし
パスワード
   よろしくお願いします。初めて質問します。

以下のコードで悩んでいます。

On Error Resume Next
Dim CN As Range
Dim FirstAddress As String
Dim pno As Range
Dim kaisha As Range
  
   Set kaisha = Worksheets("AAA").Columns("b").Find(What:=cbokaisha.Value, After:=Worksheets("AAA").Columns("b").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)

   Set CN = Worksheets("AAA").Columns("c").Find(What:=cbotantou.Value, After:=Worksheets("AAA").Columns("c").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)
    If Not kaisha Is Nothing Then
      FirstAddress = CN.Address
      Do
       If CN(1, 6).Value <> "" Then Exit Do
       Set CN = Worksheets("AAA").Columns("c").FindPrevious(CN)
      Loop While CN.Address <> FirstAddress
    End If
 
  If Not CN Is Nothing Then
    txtcn.Value = CN(1, 6).Value + 1
  Else
    txtcn.Value = "??"
  End If

ユーザーフォームで毎日の集計をしています。
シートのB列に取引会社名がありC列に担当者名がありH列にCN(領収証)番号があり
担当者のCN番号がtxtcnに表示されます。

教えていただきたいことは
1.cbokaishaで会社を選択しcbotantouで担当者を選択する。
2.txtcnに前回表示された取引会社のCN番号の次番を表示させたい。

担当者は取引会社より支給されたCNを持っています。
上記のコードでは取引会社は反映されません。

分かり難い説明ですがよろしくご教授いただけませんでしょうか。
1.

【57577】Re:検索して分岐
発言  ハチ  - 08/9/3(水) 12:42 -

引用なし
パスワード
   ▼M さん:

わからない箇所が数点あります。

【前提】
取引会社名 と 担当者名 は、
1対1の関係ですか? それとも
1対N(複数名)ですか?

>ユーザーフォームで毎日の集計をしています。
>シートのB列に取引会社名がありC列に担当者名がありH列にCN(領収証)番号があり
>担当者のCN番号がtxtcnに表示されます。
>
>教えていただきたいことは
>1.cbokaishaで会社を選択しcbotantouで担当者を選択する。

この「担当者を選択する」ですが、
コンボボックスのリストにしたいということですか?

>2.txtcnに前回表示された取引会社のCN番号の次番を表示させたい。

なにをもって「前回」と言っているのかわかりません。
データの最終行に近いデータということですか?

それとも「進む」「戻る」といった動作を、
出来るようにしたいということですか?

【57579】Re:検索して分岐
発言  kanabun  - 08/9/3(水) 13:04 -

引用なし
パスワード
   ▼M さん:

おじゃまします。

>教えていただきたいことは
>1.cbokaishaで会社を選択しcbotantouで担当者を選択する。
>2.txtcnに前回表示された取引会社のCN番号の次番を表示させたい。
>
>担当者は取引会社より支給されたCNを持っています。
>上記のコードでは取引会社は反映されません。

すでにハチさんから問い合わせがありますが、
これは ComboBoxで選択された「取引会社」 かつ 選択された「担当者」
のうち、最後の 請求書番号 を検索する、ということですか?
もしそうなら、
Findで「取引会社」を検索して、ヒットした行に対してだけ
指定の「担当者」を検索しないと、

>上記のコードでは取引会社は反映されません。

となることは、明らかですよね。
「担当者」の検索が、指定の「取引会社」の行を限定して検索されてませんから。

「取引会社」がA社 で 「担当者」が 山田 さんの行を抽出するには
AutoFilterが簡単かと思います。
抽出された行のうち、請求書番号(H列)の 一番下の行を見つけるには、
シートのH列最終行から Ctrl+[↑] とやれば、最後の番号の行へジャンプ
できます。

【57580】Re:検索して分岐
回答  M  - 08/9/3(水) 13:07 -

引用なし
パスワード
   ハチ さんご返事ありがとうございます。


>▼M さん:
>
>わからない箇所が数点あります。
>
>【前提】
>取引会社名 と 担当者名 は、
>1対1の関係ですか? それとも
>1対N(複数名)ですか?
複数名です

>>ユーザーフォームで毎日の集計をしています。
>>シートのB列に取引会社名がありC列に担当者名がありH列にCN(領収証)番号があり
>>担当者のCN番号がtxtcnに表示されます。
>>
>>教えていただきたいことは
>>1.cbokaishaで会社を選択しcbotantouで担当者を選択する。
>
>この「担当者を選択する」ですが、
>コンボボックスのリストにしたいということですか?
コンボボックスのリストは作っています。

>>2.txtcnに前回表示された取引会社のCN番号の次番を表示させたい。
>
>なにをもって「前回」と言っているのかわかりません。
>データの最終行に近いデータということですか?
データの最終行です。
>それとも「進む」「戻る」といった動作を、
>出来るようにしたいということですか?

質問の仕方が悪くご迷惑をおかけします。

現在のコードではどの取引先を選択しても担当者のCNの次番しかtxtcnに表示
されません。(コードが出来ていないのであたりまえですが)

すみませんがよろしくお願いします。

【57582】Re:検索して分岐
発言  kanabun  - 08/9/3(水) 13:50 -

引用なし
パスワード
   ▼M さん:

AutoFilter案をコードにすると、こんな感じです

'▼「B列:取引会社」と「C列:担当者」にフィルタをかける
Private Sub CommandButton1_Click()
 Dim numCN
 
 With Worksheets("AAA")
   Application.ScreenUpdating = False
   With .Range("B1", .Range("B65536").End(xlUp)).Resize(, 2)
     .AutoFilter 1, cboKaisya.Text
     .AutoFilter 2, cboTantou.Text
   End With
   numCN = .Range("H65536").End(xlUp).Value
   If IsNumeric(numCN) Then
     txtCN.Value = numCN + 1
   Else
     txtCN.Value = "??"
   End If
   .AutoFilterMode = False
   Application.ScreenUpdating = True
 End With

End Sub

※ただし、このばあいは、
cboTantou で選択された「担当者」が、
cboKaisya で選択された「取引会社」であるとは限りませんので、
一行も抽出されないことがあります。

この不具合を改善するには、
cboKaisya でリストから どれかの「取引会社」選択された時点で、
B列に選択された「取引会社」でAutoFilterをかけ、抽出行のC列「担当者」
だけ、
cboTantou リストにリストアップしておく、cboTantouリストにない「担当者」
は選ばせないようにしておくことが考えられます。

【57585】Re:検索して分岐
回答  M  - 08/9/3(水) 14:25 -

引用なし
パスワード
   kanabun さん ご返事ありがとうございます。

さすがですね!オートフイルタは気がつきませんでした。知識が浅いため
悩みました。意図した動きです。今から出かけますので夜色々検証させて
いただきます。

ハチさんの回答も待ちたいと思います。

返事は夜になりますがよろしくお願いします。

【57586】Re:検索して分岐
発言  ハチ  - 08/9/3(水) 15:03 -

引用なし
パスワード
   ▼M さん:
>ハチさんの回答も待ちたいと思います。

kanabunさんの回答が、ベストチョイスだと思いますよ^^

別案が必要なら・・・
Findの使い方は理解されているようですので、

1.A列最終行+1の位置からA列を逆順でFind
2..Offset(,1).Valueの担当者名が一致するか判定
3.一致すれば.offset(,2).Value の値が対象
4. 一致しなければ、FindNext

kanabunさんも書かれていますが、
該当のデータが必ずあることが条件になりますね。

【57595】Re:検索して分岐
お礼  M  - 08/9/3(水) 21:05 -

引用なし
パスワード
   ハチ さん kanabunさん ありがとうございます。

今回はkanabunさんのコードを使わせていただきたいと思います。
問題なく作動しました。

ありがとうございました。
又質問したときはよろしくお願いします。

【57596】Re:検索して分岐
発言  kanabun  - 08/9/3(水) 21:39 -

引用なし
パスワード
   ▼M さん:
>ハチ さん kanabunさん ありがとうございます。
>
>今回はkanabunさんのコードを使わせていただきたいと思います。
>問題なく作動しました。

追伸です(^^

> cboTantou で選択された「担当者」が、
> cboKaisha で選択された「取引会社」であるとは限りませんので、
> 一行も抽出されないことがあります。

そこで、
取引会社(cboKaisha)が選択されたら、その担当者をcboTantou にリストする
Sub cboKaisha_Change() での処理例をあげておきますね
こうしておけば、ありえない「取引会社」と「担当者」の組み合わせを
選択することがなくなります。

Private Sub cboKaisha_Change()
 Dim cc As Range
 Dim ss As String
 
 With Worksheets("AAA")
   Application.ScreenUpdating = False
   .AutoFilterMode = False
   With .Range("B1", .Range("B65536").End(xlUp))
     .AutoFilter 1, cboKaisha.Text
     On Error Resume Next
     Set cc = Intersect(.Offset(, 1), .Offset(1, 1))
     On Error GoTo 0
     If Not cc Is Nothing Then
       cc.Copy
       With New DataObject '選択された「取引会社」の全担当者をリスト
         .GetFromClipboard
         ss = .GetText
         Debug.Print ss
         cboTantou.RowSource = ""
         cboTantou.List = Split(ss, vbCrLf)
       End With
       Application.CutCopyMode = False
     End If
   End With
   .AutoFilterMode = False
   Application.ScreenUpdating = True
 End With
  
End Sub

※ Private Sub CommandButton1_Click()
のほうは 先のコードのまま 「取引会社」と「担当者」の2列で AutoFilter
をかけます。

【57598】Re:検索して分岐
発言  kanabun  - 08/9/3(水) 22:19 -

引用なし
パスワード
   ▼M さん:

考えてみれば、cboKaishaのChangeで cboTantouの担当者リストをつくるとき、
いっしょに CN請求書番号 もcboTantouのリストに表示しておけば (下へつづく)

Private Sub cboKaisha_Change()
 Dim cc As Range
 Dim ss As String, i As Long
 Dim vTantou, vCN, numCN
 Dim dic As Object
 
 With Worksheets("AAA")
   Application.ScreenUpdating = False
   .AutoFilterMode = False
   With .Range("B1", .Range("B65536").End(xlUp))
     .AutoFilter 1, cboKaisha.Text
     On Error Resume Next
     Set cc = Intersect(.Offset(, 1), .Offset(1, 1))
     On Error GoTo 0
     If cc Is Nothing Then
       cboTantou.Clear
       cboTantou.Text = ""
     Else
       cc.Copy
       With New DataObject '選択された「取引会社」の全担当者をリスト
         .GetFromClipboard
         ss = .GetText
         vTantou = Split(ss, vbCrLf)
         .Clear
                 '同時に担当者の最終CN番号を表示
         cc.Offset(, 5).Copy
         .GetFromClipboard
         ss = .GetText
         vCN = Split(ss, vbCrLf)
       End With
       Application.CutCopyMode = False
       Set dic = CreateObject("Scripting.Dictionary")
       For i = 0 To UBound(vTantou) - 1
         ss = vTantou(i)
         If dic.Exists(ss) Then
           numCN = dic(ss)
           If numCN < vCN(i) Then dic(ss) = vCN(i)
         Else
           dic(ss) = vCN(i)
         End If
       Next
       With cboTantou
         .ColumnCount = 2
         .ListWidth = 180
         .List = Application.Transpose(Array(dic.Keys, dic.Items))
       End With
       Set dic = Nothing
     End If
   End With
   .AutoFilterMode = False
   Application.ScreenUpdating = True
 End With
  
End Sub

こうしておけば、ユーザーが担当者リストのどれかを選択すると同時に、
テキストボックス txtCN に 最終番号+1が表示されますから、もう
ボタンは不要となりますね♪

Private Sub cboTantou_Change()
  Dim numCN
  With cboTantou
    numCN = .List(.ListIndex, 1)
  End With
  If IsNumeric(numCN) Then
    txtCN.Text = numCN + 1
  Else
    txtCN.Text = "??"
  End If
End Sub

【57603】Re:検索して分岐
お礼  M  - 08/9/4(木) 8:07 -

引用なし
パスワード
   kanabunさん 再度のご返事ありがとうございます。

すばらしいコードですね! よく勉強させていただきます。

又質問したときはよろしくお願いします。

【57686】Re:検索して分岐
質問  M  - 08/9/9(火) 17:25 -

引用なし
パスワード
   お世話になります。

前回kanabunさん ハチさんには大変お世話になりました。
オートフイルタを使ったコードで目的の動きはできました。現在使わせていただいております。

いまFINDを使う事が多いのですが下記のコードを教えていただけませんでしょうか。
FINDの勉強をしたいと思います。

On Error Resume Next
Dim CN As Range
Dim FirstAddress As String
Dim pno As Range
Dim kaisha As Range
  
   Set kaisha = Worksheets("AAA").Columns("b").Find(What:=cbokaisha.Value, After:=Worksheets("AAA").Columns("b").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)

   Set CN = Worksheets("AAA").Columns("c").Find(What:=cbotantou.Value, After:=Worksheets("AAA").Columns("c").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)
    If Not kaisha Is Nothing Then
      FirstAddress = CN.Address
      Do
       If CN(1, 6).Value <> "" Then Exit Do
       Set CN = Worksheets("AAA").Columns("c").FindPrevious(CN)
      Loop While CN.Address <> FirstAddress
    End If
 
  If Not CN Is Nothing Then
    txtcn.Value = CN(1, 6).Value + 1
  Else
    txtcn.Value = "??"
  End If

ユーザーフォームで毎日の集計をしています。
シートのB列に取引会社名がありC列に担当者名がありH列にCN(領収証)番号があり
担当者のCN番号がtxtcnに表示されます。

担当者は取引会社より支給されたCNを持っています。
上記のコードではコードがないので取引会社は反映されません。

教えていただきたいことはcbokaishaで会社を選択しcbotantouで担当者を選択したとき
1.txtcnに前回表示された取引会社の担当者のCN番号の次番を表示させたい。

【57687】Re:検索して分岐
質問  kanabun  - 08/9/9(火) 19:52 -

引用なし
パスワード
   ▼M さん:
>前回kanabunさん ハチさんには大変お世話になりました。
>オートフイルタを使ったコードで目的の動きはできました。現在使わせていただいております。

それはどうも。


>いまFINDを使う事が多いのですが下記のコードを教えていただけませんでしょうか。
>FINDの勉強をしたいと思います。

> (中略)

>ユーザーフォームで毎日の集計をしています。
>シートのB列に取引会社名がありC列に担当者名がありH列にCN(領収証)番号があり
>担当者のCN番号がtxtcnに表示されます。
>
>担当者は取引会社より支給されたCNを持っています。
>上記のコードではコードがないので取引会社は反映されません。
>
>教えていただきたいことはcbokaishaで会社を選択しcbotantouで担当者を選択したとき
>1.txtcnに前回表示された取引会社の担当者のCN番号の次番を表示させたい。

あの、このご質問は、一番最初のご質問と、どこが どう、ちがうのでしょうか?

Find、FindNext〜Loop を使っては
2つの項目の AND検索は不可能であるということで、オートフィルタになったのでは
ありませんでしたか?

【57688】Re:検索して分岐
発言  kanabun  - 08/9/9(火) 20:34 -

引用なし
パスワード
   ▼M さん:

もしFindを使って検索したいなら、
B列から 会社名を Find〜Loopで検索する中で、指定の会社名がヒットするたびに
ヒットしたセルの右のセルを調べ、その値が指定の担当者だったら、CN番号を
セットしてFind〜Loopを脱出するようなコードを組んでください。

Sub もしFindを使うなら()
 Dim kaisha As Range
 Dim FirstAddress As String
 Dim 会社名 As String
 Dim 担当者名 As String
 Dim ok As Boolean
 
 会社名 = cboKaisha.Text
 担当者名 = cboTantou.Text
 With Worksheets("AAA")
   Set kaisha = .Columns(2).Find(会社名, _
       After:=.Columns(2).Cells(1), _
       LookIn:=xlValues, LookAt:=xlWhole, _
       SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
   If Not kaisha Is Nothing Then
     FirstAddress = kaisha.Address
     Do
       If kaisha.Item(1, 2).Value = 担当者名 Then
         txtCN.Text = kaisha.Item(1, 7).Value + 1
         ok = True
         Exit Do
       End If
       Set kaisha = .Columns(2).FindNext(kaisha)
     Loop Until kaisha.Address = FirstAddress
   End If
 End With
 If Not ok Then
   MsgBox "指定会社の指定担当者が見つかりませんでした"
   txtCN.Text = ""
 End If
End Sub

【57689】Re:検索して分岐
お礼  M  - 08/9/9(火) 20:42 -

引用なし
パスワード
   kanabun さん ありがとうございます。
>>前回kanabunさん ハチさんには大変お世話になりました。
>>オートフイルタを使ったコードで目的の動きはできました。現在使わせていただいております。
>
>それはどうも。

>あの、このご質問は、一番最初のご質問と、どこが どう、ちがうのでしょうか?
kanabun さんのコードで十分です。今FINDを勉強していまして今のコードで出来ないかと思いました。
>Find、FindNext〜Loop を使っては
>2つの項目の AND検索は不可能であるということで、オートフィルタになったのでは
>ありませんでしたか?
私の知識不足でFind、FindNext〜Loop を使っては不可能であることをわかりませんでした。

すみません。折角いいコード教えていただきながら理解不足で申し訳ありません。
今後とも見捨てずよろしくお願いします。

【57690】Re:検索して分岐
お礼  M  - 08/9/9(火) 20:55 -

引用なし
パスワード
   kanabun さん レスがかぶったようです。

今から検証してみたいと思います。

いつもご返事いただきありがとうございます。

【57697】Re:検索して分岐
発言  M  - 08/9/10(水) 8:20 -

引用なし
パスワード
   おはようございます。

txtcnの番号が最後の番号+1でなく最初の番号+1になるようです。

今調べていますが???

【57698】Re:検索して分岐
発言  ハチ  - 08/9/10(水) 8:51 -

引用なし
パスワード
   ▼M さん:
>おはようございます。
>
>txtcnの番号が最後の番号+1でなく最初の番号+1になるようです。
>
>今調べていますが???

横から失礼します。

試してませんが、
SearchOrder:=xlByRows → SearchOrder:=xlByColumns
で、どうでしょう?

Findの引数は、良く理解して使ってください。

39228にichinoseさんが書かれた、
わかりやすい説明サンプルがありますよ。

【57704】Re:検索して分岐
発言  M  - 08/9/10(水) 10:31 -

引用なし
パスワード
   ▼ハチ さん いつもありがとうございます。

>SearchOrder:=xlByRows → SearchOrder:=xlByColumns
>で、どうでしょう?
変更しましたが変わらないようです。

cbotantouに5人の名前がありますが1番目と2番目の場合最初の番号+1になり
3番目の場合は最後の番号+1(このようにしたい)になります。
4番目と5番目は入力がないためメッセージがでます。

>39228にichinoseさんが書かれた、
>わかりやすい説明サンプルがありますよ。
ありがとうございます。

【57705】Re:検索して分岐
発言  ハチ  - 08/9/10(水) 10:42 -

引用なし
パスワード
   ▼M さん:
>cbotantouに5人の名前がありますが1番目と2番目の場合最初の番号+1になり
>3番目の場合は最後の番号+1(このようにしたい)になります。
>4番目と5番目は入力がないためメッセージがでます。

つまり、データに依存している ということですね。
こういった掲示板では、それを検証する手立てはありませんので、
自分で調査するしかありません。

Findの 半角全角 や 完全一致などオプションを、
ひとつづつ、検証していくのが良いと思います。

【57713】Re:検索して分岐
発言  kanabun  - 08/9/10(水) 12:50 -

引用なし
パスワード
   ▼M さん:


> cbotantouに5人の名前がありますが
> (中略)
> 4番目と5番目は入力がないためメッセージがでます。

【57596】や
【57598】 に揚げた AutoFilter方式でやったばあい、
cboKaisha から会社をひとつ選択したとき、対応する担当者だけを
cboTantou にセットしなおしますから、
そのようなことにはならないと思います。

手動で、オートフィルタを担当者列にかけたとき、
見出し行の▼ドロップダウンのリストに表示されるのは
5人の担当者名だけですか?

【57715】Re:検索して分岐
発言  M  - 08/9/10(水) 14:06 -

引用なし
パスワード
   ▼kanabun さんご面倒をおかけします。

>手動で、オートフィルタを担当者列にかけたとき、
>見出し行の▼ドロップダウンのリストに表示されるのは
>5人の担当者名だけですか?
現在売り上げがある担当者は3人ですのでドロップダウンのリストに表示されるのは
3人の名前だけです。すべて 空白セル 空白以外のセルとかは表示されますが。

cbotantouには5名の名前があります。ダミーで5人全員入力したら5人の名前が出ます。

【57716】Re:検索して分岐
発言  M  - 08/9/10(水) 14:09 -

引用なし
パスワード
   4番目と5番目は入力がないためメッセージがでます。
   ↑
入力がないので当然だとおもいます。(メッセージボックス)

【57721】【すいません】Re:検索して分岐
発言  ハチ  - 08/9/10(水) 15:16 -

引用なし
パスワード
   M さん、kanabunさん


気になったので、少し確認してみたのですが、
自分の最初の発言が誤りのモトでした。すいません。

>4. 一致しなければ、FindNext
ここです。

「FindNext」を実行することで、
前回の検索条件を引き継いで、
逆順でFindするとばかり思っていたのですが、
「FindPrevious」にしないとダメでした。
FindNextだと上から検索に行ってしまいます。

FindNext → FindPrevious

お騒がせしました。

【57722】Re:【すいません】Re:検索して分岐
発言  kanabun  - 08/9/10(水) 15:28 -

引用なし
パスワード
   ▼ハチ さん:

>>4. 一致しなければ、FindNext
>ここです。

>「FindPrevious」にしないとダメでした。
>FindNextだと上から検索に行ってしまいます。
>
>FindNext → FindPrevious
>
>お騒がせしました。

あっ、それ気が付いてませんでした。
ハチ さん、ご指摘ありありがとうございます。

M さん
申し訳ないっす。Findで2つの同時検索はむつかしいことの説明ばかりに
関心が行ってなくて、そちらには目が行ってませんでした。m(__)m

【57726】Re:【すいません】Re:検索して分岐
お礼  M  - 08/9/10(水) 17:14 -

引用なし
パスワード
   ▼kanabun さん▼ハチ さん ありがとうございます。

>>お騒がせしました。
とんでもございません。

>M さん
>申し訳ないっす。Findで2つの同時検索はむつかしいことの説明ばかりに
>関心が行ってなくて、そちらには目が行ってませんでした。m(__)m

とんでもございません。おかげさまで大変勉強になりました。

今後ともよろしくお願いします。

【57735】Re:検索して分岐
発言  kanabun  - 08/9/11(木) 9:12 -

引用なし
パスワード
   ▼M さん:

Findメソッド案ですが、、

「会社名」と「担当者」名をAND検索をおこなうとき、
シートの作業列(使っていない列)たとえば、[Q列] に
B列とC列をある区切り文字 (たとえば "|")で結合した値を
書き込んでおくと、
一回のFind検索をかけるだけで、最も最近の指定の
「会社」と「担当者」の組みをみつけることができます。
(Do〜Loopが不要になります)

<準備>
作業列(以下では Q列としています)に、
[Q2]セルに、  =B2&"|"&C2
と数式を打ち込み、データ最終行までCopy しておきます


Sub もしFindを使うなら_その2()

 Dim c As Range
 Dim 会社名 As String
 Dim 担当者名 As String
 Dim 検索値 As String
 Dim ok As Boolean
 
 If cboKaisha.ListIndex > -1 Then
   会社名 = cboKaisha.Text
   If cboTantou.ListIndex > -1 Then
     担当者名 = cboTantou.Text
     検索値 = 会社名 & "|" & 担当者名
     ok = True
   End If
 End If
 If Not ok Then Exit Sub
 
 With Worksheets("AAA")
   '作業列を下から検索 '検索値 = 会社名 & "|" & 担当者名
   Set c = .Columns("Q").Find(検索値, _
       After:=.Range("Q1"), _
       LookIn:=xlValues, LookAt:=xlWhole, _
       SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
   If Not c Is Nothing Then
     txtCN.Text = .Cells(c.Row, "H").Value + 1
   Else
     MsgBox "指定会社の指定担当者が見つかりませんでした"
     txtCN.Text = ""
   End If
 End With
End Sub


いくつか変数を間違えていたので、一度削除して、再掲しました。m(__)m

【57741】Re:検索して分岐
お礼  M  - 08/9/11(木) 18:15 -

引用なし
パスワード
   kanabunさん 見てなくて返事が遅くなりすみません。

再度のご提示ありがとうございます。何回も申し訳なく思っています。

FINDを使った2件とも検証しまして動いております。
只担当者がある商品を売った場合CN番号がないため(証明書番号)空欄になります。
次に担当者がCNを切ったときは1と表示され連番になりません。仕方のないことなのでしょうか。

オートフイルタを使った場合は連番で表示されます。

以上のようなことからFINDを使うコードよりオートフイルタを使ったコードのほうが
使い勝手がいいようにあります。

仕事で非常に効率がよくなりました。ありがとうございます。

【57742】Re:検索して分岐
発言  kanabun  - 08/9/11(木) 19:25 -

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

> 只担当者がある商品を売った場合CN番号がないため
> (証明書番号)空欄になります。
> 次に担当者がCNを切ったときは1と表示され連番になりません。
> 仕方のないことなのでしょうか。

> オートフイルタを使った場合は連番で表示されます。
というのは
【57598】 に書き込んだコードのことだったでしょうか?
これは、実は オートフィルタ方式ではありません。
必要な列の値を全行メモリ(配列)にいれ、配列内で
処理しています。

このコードと他のコードの CN番号 を求める方法の違いは、
他のコードが 「会社名」と「担当者名」の一致する行の
▲「最後の行」を求めているのに対して、

【57598】では、
> cboKaishaのChangeで cboTantouの担当者リストをつくるとき、
> いっしょに CN請求書番号 もcboTantouのリストに表示しておけば
>
  (コードの主要部分)
>     Set dic = CreateObject("Scripting.Dictionary")
>     For i = 0 To UBound(vTantou) - 1
>       ss = vTantou(i)
>       If dic.Exists(ss) Then
>         numCN = dic(ss)
>         If numCN < vCN(i) Then dic(ss) = vCN(i)
>       Else
>         dic(ss) = vCN(i)
>       End If
>     Next

> ユーザーが担当者リストのどれかを選択すると同時に、
> テキストボックス txtCN に 最終番号+1が表示されます

と説明にあるように、
「会社名」と「担当者名」の一致する行の CN番号の★[最大値]を
dictionaryにあらかじめ格納しているからです。

ですから、フィルタ方式でも、Find方式でも、
「会社名」と「担当者名」の一致する行の ▲「最後の行」でなく、
該当行のうち CN番号の★[最大値]を求めるようにすれば、
どの方式でも、その点に関しては不具合は出なくなると思います。

▼最後に書き込んだ
>        Sub もしFindを使うなら_その2()
をこの方式に改良すると、次のような感じです。

Sub もしFindを使うなら_その3() '最終CN値でなく、最大CN番号を求める

 Dim c As Range, RngSearch As Range
 Dim 会社名 As String
 Dim 担当者名 As String
 Dim 検索値 As String
 Dim numCN, hCN
 Dim ok As Boolean
 Dim firstAddress As String

 If cboKaisha.ListIndex > -1 Then
   会社名 = cboKaisha.Text
   If cboTantou.ListIndex > -1 Then
     担当者名 = cboTantou.Text
     検索値 = 会社名 & "|" & 担当者名
     ok = True
   End If
 End If
 If Not ok Then Exit Sub

 With Worksheets("AAA")
   Set RngSearch = .Range("Q2", Cells(.Rows.Count).End(xlUp))
 
   '作業列を一巡検索 検索値 = 会社名 & "|" & 担当者名
   Set c = rngserch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
   If Not c Is Nothing Then
     firstAddress = c.Address
     Do
       hCN = .Cells(c.Row, "H").Value
       If IsNumeric(hCN) Then
         If numCN < hCN Then numCN = hCN '最大CN番号さがし
       End If
       Set c = RngSearch.FindNext(c)
     Loop Until c.Address = firstAddress
     txtCN.Text = numCN + 1
   Else
     MsgBox "指定会社の指定担当者が見つかりませんでした"
     txtCN.Text = ""
   End If
 End With
End Sub

【57751】Re:検索して分岐
発言  M  - 08/9/12(金) 7:18 -

引用なし
パスワード
   おはようございます。

>Set c = rngserch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
がエラーになったので
Set c = RngSearch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
に変更しましたが動かないようでが。

【57688】の最大値の求め方を教えていただけませんでしょうか。
昨日から色々試しているのですが思った最大値が求められません。
何回も申し訳ありません。

【57752】Re:検索して分岐
発言  kanabun  - 08/9/12(金) 8:57 -

引用なし
パスワード
   ▼M さん:

>>Set c = rngserch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
>がエラーになったので
>Set c = RngSearch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
>に変更しましたが動かないようでが。
>
失礼しました。
1.Search のタイプミスでした
2.Cells(.Rows.Count, "Q") ----- "Q"が抜けてました

 With Worksheets("AAA")
   Set RngSearch = .Range("Q2", Cells(.Rows.Count, "Q").End(xlUp))
 
   '作業列を一巡検索 検索値 = 会社名 & "|" & 担当者名
   Set c = RngSearch.Find(検索値, , LookIn:=xlValues, LookAt:=xlWhole)
   If Not c Is Nothing Then
     firstAddress = c.Address


>【57688】の最大値の求め方を教えていただけませんでしょうか。
>昨日から色々試しているのですが思った最大値が求められません。

> If numCN < vCN(i) Then dic(ss) = vCN(i)

H列の値 vCN(i) が 現在の最大値よりも大きければ その値を あたらしい
最大値として行っているわけですが、、、、

たとえば
会社名「ABC商事」 担当者「田中」の行 が 以下のようだったとすると
H列には どのような値が入ってますか?
うまくいかない例をあげてください。


   B列     C列      H列
4  ABC商事  田中  
8  ABC商事  田中
20 ABC商事  田中
21 ABC商事  田中
45 ABC商事  田中

   

【57787】Re:検索して分岐
お礼  M  - 08/9/13(土) 12:40 -

引用なし
パスワード
   kanabunさん 出張してまして返事が遅れてすみません。

大事なことをうっかりしてました。

例 0869−38061
    ↑    ↑
    綴NO  CN番号
1冊20枚のCNです。使い終わって新しいCNは番号が大きいとは限りません。
最大値を求めても無意味になります。

教えていただいたフイルタのコードがベストのような気がします。
問題なく動きますのでフイルタのコードを使わせていただきと思います。

色々教えていただきありがとうございます。

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