Excel VBA質問箱 IV

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

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


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

4228 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

▼

【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("ＡＡＡ").Columns("b").Find(What:=cbokaisha.Value, After:=Worksheets("ＡＡＡ").Columns("b").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
　　　 SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)

　　 Set CN = Worksheets("ＡＡＡ").Columns("c").Find(What:=cbotantou.Value, After:=Worksheets("ＡＡＡ").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("ＡＡＡ").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

ユーザーフォームで毎日の集計をしています。
シートのＢ列に取引会社名がありＣ列に担当者名がありＨ列にＣＮ（領収証）番号があり
担当者のＣＮ番号がtxtcnに表示されます。

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

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

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


	【57577】Re:検索して分岐

ハチ - 08/9/3(水) 12:42 -


	引用なし

	パスワード

▼M さん：

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

【前提】
取引会社名　と　担当者名　は、
１対１の関係ですか？　それとも
１対Ｎ(複数名）ですか？

>ユーザーフォームで毎日の集計をしています。
>シートのＢ列に取引会社名がありＣ列に担当者名がありＨ列にＣＮ（領収証）番号があり
>担当者のＣＮ番号がtxtcnに表示されます。
>
>教えていただきたいことは
>１．cbokaishaで会社を選択しcbotantouで担当者を選択する。

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

>２．txtcnに前回表示された取引会社のＣＮ番号の次番を表示させたい。

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

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


	【57579】Re:検索して分岐

kanabun - 08/9/3(水) 13:04 -


	引用なし

	パスワード

▼M さん：

おじゃまします。

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

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

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

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

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


	【57580】Re:検索して分岐

M - 08/9/3(水) 13:07 -


	引用なし

	パスワード

ハチさんご返事ありがとうございます。

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

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

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

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

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

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


	【57582】Re:検索して分岐

kanabun - 08/9/3(水) 13:50 -


	引用なし

	パスワード

▼M さん：

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

'▼「B列：取引会社」と「C列：担当者」にフィルタをかける
Private Sub CommandButton1_Click()
　Dim numCN
　
　With Worksheets("ＡＡＡ")
　　　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の使い方は理解されているようですので、

１．Ａ列最終行＋１の位置からＡ列を逆順でFind
２．.Offset(,1).Valueの担当者名が一致するか判定
３．一致すれば.offset(,2).Value の値が対象
４.　一致しなければ、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("ＡＡＡ")
　　　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("ＡＡＡ")
　　　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 に最終番号＋１が表示されますから、もう
ボタンは不要となりますね♪
▼
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("ＡＡＡ").Columns("b").Find(What:=cbokaisha.Value, After:=Worksheets("ＡＡＡ").Columns("b").Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
　　　 SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)

　　 Set CN = Worksheets("ＡＡＡ").Columns("c").Find(What:=cbotantou.Value, After:=Worksheets("ＡＡＡ").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("ＡＡＡ").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

ユーザーフォームで毎日の集計をしています。
シートのＢ列に取引会社名がありＣ列に担当者名がありＨ列にＣＮ（領収証）番号があり
担当者のＣＮ番号がtxtcnに表示されます。

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

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


	【57687】Re:検索して分岐

kanabun - 08/9/9(火) 19:52 -


	引用なし

	パスワード

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

それはどうも。

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

> （中略）

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

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

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("ＡＡＡ")
　　　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の番号が最後の番号＋１でなく最初の番号＋１になるようです。

今調べていますが？？？


	【57698】Re:検索して分岐

ハチ - 08/9/10(水) 8:51 -


	引用なし

	パスワード

▼M さん：
>おはようございます。
>
>txtcnの番号が最後の番号＋１でなく最初の番号＋１になるようです。
>
>今調べていますが？？？

横から失礼します。

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

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

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


	【57704】Re:検索して分岐

M - 08/9/10(水) 10:31 -


	引用なし

	パスワード

▼ハチさんいつもありがとうございます。

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

cbotantouに５人の名前がありますが１番目と２番目の場合最初の番号＋１になり
３番目の場合は最後の番号＋１（このようにしたい）になります。
４番目と５番目は入力がないためメッセージがでます。

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


	【57705】Re:検索して分岐

ハチ - 08/9/10(水) 10:42 -


	引用なし

	パスワード

▼M さん：
>cbotantouに５人の名前がありますが１番目と２番目の場合最初の番号＋１になり
>３番目の場合は最後の番号＋１（このようにしたい）になります。
>４番目と５番目は入力がないためメッセージがでます。

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

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


	【57713】Re:検索して分岐

kanabun - 08/9/10(水) 12:50 -


	引用なし

	パスワード

▼M さん：

> cbotantouに５人の名前がありますが
> （中略）
> ４番目と５番目は入力がないためメッセージがでます。

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

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


	【57715】Re:検索して分岐

M - 08/9/10(水) 14:06 -


	引用なし

	パスワード

▼kanabun さんご面倒をおかけします。

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

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


	【57716】Re:検索して分岐

M - 08/9/10(水) 14:09 -


	引用なし

	パスワード

４番目と５番目は入力がないためメッセージがでます。
　　　↑
入力がないので当然だとおもいます。（メッセージボックス）


	【57721】【すいません】Re:検索して分岐

ハチ - 08/9/10(水) 15:16 -


	引用なし

	パスワード

M さん、kanabunさん

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

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

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

FindNext → FindPrevious

お騒がせしました。


	【57722】Re:【すいません】Re:検索して分岐

kanabun - 08/9/10(水) 15:28 -


	引用なし

	パスワード

▼ハチさん：

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

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

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

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


	【57726】Re:【すいません】Re:検索して分岐

M - 08/9/10(水) 17:14 -


	引用なし

	パスワード

▼kanabun さん▼ハチさん　ありがとうございます。

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

>M さん
>申し訳ないっす。Findで２つの同時検索はむつかしいことの説明ばかりに
>関心が行ってなくて、そちらには目が行ってませんでした。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("ＡＡＡ")
　　　'作業列を下から検索　'検索値 = 会社名 & "|" & 担当者名
　　　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を使った２件とも検証しまして動いております。
只担当者がある商品を売った場合CN番号がないため（証明書番号）空欄になります。
次に担当者がCNを切ったときは１と表示され連番になりません。仕方のないことなのでしょうか。

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

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

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


	【57742】Re:検索して分岐

kanabun - 08/9/11(木) 19:25 -


	引用なし

	パスワード

▼M さん：
こんばんは。

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

> オートフイルタを使った場合は連番で表示されます。
というのは
【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 に最終番号＋１が表示されます

と説明にあるように、
「会社名」と「担当者名」の一致する行の 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("ＡＡＡ")
　　　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)
に変更しましたが動かないようでが。

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


	【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("ＡＡＡ")
　　　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

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

> 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さん　出張してまして返事が遅れてすみません。

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

例　０８６９－３８０６１
　　　　↑　　　　↑
　　　　綴NO　　CN番号
１冊２０枚のCNです。使い終わって新しいCNは番号が大きいとは限りません。
最大値を求めても無意味になります。

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

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


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

4228 / 13644 ﾂﾘｰ

←次へ　｜　前へ→

ページ：

┃

記事番号：

(SS)C-BOARD v3.8 is Free