Excel VBA質問箱 IV

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

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


1098 / 13645 ツリー ←次へ | 前へ→

【76332】2つの値検索して取得 you 14/11/1(土) 0:31 質問[未読]
【76336】Re:2つの値検索して取得 γ 14/11/1(土) 10:35 発言[未読]
【76339】Re:2つの値検索して取得 you 14/11/1(土) 15:07 質問[未読]
【76340】Re:2つの値検索して取得 γ 14/11/1(土) 17:26 発言[未読]
【76341】Re:2つの値検索して取得 you 14/11/1(土) 18:58 質問[未読]
【76343】Re:2つの値検索して取得 γ 14/11/1(土) 20:47 発言[未読]
【76347】Re:2つの値検索して取得 you 14/11/1(土) 22:04 お礼[未読]

【76332】2つの値検索して取得
質問  you  - 14/11/1(土) 0:31 -

引用なし
パスワード
   2つの値を検索して値を返すにはどうしたらよいのでしょうか
教えてください。シート2に下記の一覧があります。
シート2(X1)
セルA列  B列 C列  D列  E列
佐藤   太郎 社名 住所  電話番号
・    ・   ・  ・    ・
・    ・   ・  ・    ・
・    ・   ・  ・    ・
・    ・   ・  ・    ・
佐藤   花子  社名 住所  電話番号
・    ・   ・  ・    ・
・    ・   ・  ・    ・
・    ・   ・  ・    ・
・    ・   ・  ・    ・
鈴木   三郎  社名 住所  電話番号

検索用のシート下記に値を入力
シート1(X2)
セルA列1行目
姓(佐藤)を入力      
セルA列2行目
名(花子)を入力

マクロを実行すると検索した値をシート1に返したいのです。
シート1(X2)
セルA列3行目
佐藤花子の会社名     
セルA列4行目
佐藤花子の住所
佐藤花子の電話番号

こんな感じで書いていますが1つの値しか検索できません。
また、値がなかったらデバッグがでます。全然できてませんが
どなたかよろしくお願いします。

Sub 検索結果()
'
Dim ANSYL As Integer
Dim TanNo, FIND As String
Dim X1, X2 As Worksheet

'検索シート(X2)、一覧シート(X1)を省略形
  Set X1 = Worksheets("一覧")
  Set X2 = Worksheets("検索")

'指名を検索
  TanNo = X2.Range("A1")
  
'一覧シートの指名を検索
  X1.Activate
  Set FND = X1.Columns("A:A").FIND(what:=TanNo, lookat:=xlWhole)
  
'指名が存在すれば、アクティブにし、行番号を取得

    X1.Activate
    X1.Range("A:A").Select
    Selection.FIND(what:=TanNo, lookat:=xlWhole).Activate
      ANSYL = ActiveCell.Row

'検索シートに結果を表示
    X2.Cells(1, 3) = X1.Cells(ANSYL, 3) '社名
    X2.Cells(1, 4) = X1.Cells(ANSYL, 4) '住所
    X2.Cells(1, 5) = X1.Cells(ANSYL, 5) '電話番号
X2.Activate
Range("D4").Select

End Sub

【76336】Re:2つの値検索して取得
発言  γ  - 14/11/1(土) 10:35 -

引用なし
パスワード
   姓と名の間のスペースに規則性があるなら、
検索する姓と名を連結してから、完全一致で検索すればヒットするはずです。

仮にそうした規則性がなく、姓と名とを別々にチェックせざるを得ないなら、
部分一致検索を使って、次のような感じになるのではないですか?

(ただし、姓と名のケースによっては上手くいかないケースがあるかもしれません。
例えば、「太」で「太一」にマッチしてしまうとかですね。
そうしたケースが頻出するなら、そのための対応が必要ですが、
とりあえずコードを作ってみました。参考になれば。)

Sub 検索結果()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim name1 As String
  Dim name2 As String
  Dim fnd As Range
  Dim flag As Boolean
  Dim firstAddress As String
  Dim r As Long

  '検索シート(ws2)、一覧シート(ws1)を省略形
  Set ws1 = Worksheets("一覧")
  Set ws2 = Worksheets("検索")

  '氏名を検索
  name1 = ws2.Range("A1").Value
  name2 = ws2.Range("A2").Value

  '一覧シートの氏名を検索
  flag = False
  With ws1.Columns("A")
    Set fnd = .FIND(what:=name1, lookat:=xlPart)
    If Not fnd Is Nothing Then
      firstAddress = fnd.Address
      If InStr(fnd.Value, name2) > 0 Then
        flag = True
      Else
        Do
          Set fnd = .FindNext(fnd)
          If InStr(fnd.Value, name2) > 0 Then
            flag = True
            Exit Do
          End If
        Loop While Not fnd Is Nothing And fnd.Address <> firstAddress
      End If
    End If
  End With

  '氏名が存在すれば、行番号を取得
  If flag = True Then
    r = fnd.Row
    '検索シートに結果を表示
    ws2.Cells(1, 3).Value = ws1.Cells(r, 3).Value  '社名
    ws2.Cells(1, 4).Value = ws1.Cells(r, 4).Value  '住所
    ws2.Cells(1, 5).Value = ws1.Cells(r, 5).Value  '電話番号
    ws2.Activate
  Else
    MsgBox "該当するデータがありません"
  End If
End Sub


本来は、姓と名の間を(例えば)全角空白ひとつに限定することとし、
これに沿って元データを作り直しておくのが、良いと思います。
こうすれば、検索する姓と名を全角空白を含めて連結してから、
完全一致で検索すればヒットするわけですから、悩む必要がありません。

なお、貴兄の変数宣言の方法はまちがいです。
>Dim TanNo, FIND As String
とすると、String型なのはFindだけで、TanNoはVariant型です。
それぞれ型宣言する必要があります。

【76339】Re:2つの値検索して取得
質問  you  - 14/11/1(土) 15:07 -

引用なし
パスワード
   rさん、回答ありがとうございます。
やはり同じ姓があると該当なしになってしまいます。
完全一致にするため

一覧にA列は姓名を結合しました
A列         B列  C列  D列   E列  F列
姓名(スペースなし) 姓  名  会社名  住所 電話番号

検索だけは
A列1行目に姓、A列2行目に名を入力してマクロを走らせたいのですが
何か良い手はないでしょうか

あまりマクロが分かってないですいません。
お忙しいところよろしくお願いします。

【76340】Re:2つの値検索して取得
発言  γ  - 14/11/1(土) 17:26 -

引用なし
パスワード
   >完全一致にするため
>
>一覧にA列は姓名を結合しました
それなら、あなたのコードにおいて、
TanNo = X2.Range("A1").Value & X2.Range("A2").Value
として検索すればいいんじゃないですか?

>やはり同じ姓があると該当なしになってしまいます。
私のところではそうなりません。
データの例をあげてもらえば議論できますが、
連結データを作ったのなら、無駄な議論になるかも知れない。

それはさて置いても、折角コードを作ったので、
・インデントのつけかた、
・.Valueなどは省略しないほうがいいこと
・型宣言の話
など、参考にしてもらいたいですな。

【76341】Re:2つの値検索して取得
質問  you  - 14/11/1(土) 18:58 -

引用なし
パスワード
   γ さんありがとうございます。
ご指摘を参考に直してみたのですが
Selection.FIND(what:=TanNo, lookat:=xlWhole).Activate
でデバックがでます。

Sub 検索結果()
'
Dim ANSYL As Integer
Dim TanNo, FIND As Variant
Dim X1, X2 As Worksheet

'検索シート(X2)、一覧シートX1)を省略形
  Set X1 = Worksheets("DB")
  Set X2 = Worksheets("検索")

'姓名を検索
  TanNo = X2.Range("A1").Value & X2.Range("A2").Value
  
'一覧シートの中で姓名を検索
  X1.Activate
  Set FND = X1.Columns("A:A").FIND(what:=TanNo, lookat:=xlWhole)
  
'姓名が存在すれば、アクティブにし、行番号を取得

    X1.Activate
    X1.Range("A:A").Select
    Selection.FIND(what:=TanNo, lookat:=xlWhole).Activate
      ANSYL = ActiveCell.Row

'検索シートに結果を表示
    X2.Cells(1, 3) = X1.Cells(ANSYL, 4) '社名
    X2.Cells(1, 4) = X1.Cells(ANSYL, 5) '住所
    X2.Cells(1, 5) = X1.Cells(ANSYL, 6) '電話番号

X2.Activate
Range("D4").Select

End Sub

また、作成していただいたマクロなんですが、A1に姓、A2に名を入れると「該当なし」A2の名をセルから消すとマクロが実行されA3、A4、A5に値が返されます。

見よう見まねで作成しているのでVBAがよく分かっていません。
議論にならず。申し訳ありません。
よろしくお願いします。

【76343】Re:2つの値検索して取得
発言  γ  - 14/11/1(土) 20:47 -

引用なし
パスワード
   ▼you さん:
>でデバックがでます。
だけでは情報が不十分です。
どんなメッセージが出ているかを書かないと、
うまくいきません、と同レベルです。

シート上のデータが想定しているものと違うかも知れませんし、
こちらでは打つ手がありません。

ステップ実行
ht tp://hp.vector.co.jp/authors/VA016119/step/step01.html
を参考にして、ご自分でデバッグ作業をすることが必須です。

・TanNoという変数に想定どおりの文字列がセットされたか、
・本当は、どのセルにマッチするはずなのか。
・そのセルと、TanNoとは本当に一致しているのかどうか、
これらを、ご自分で確認しないと、前には進みません。

コードの問題というよりも、
想定したデータとは異なるデータになっているような気がします。

ポイントは、
・デバッグ手法をマスターすること
です。

# ちなみに、私が提案したコーディング上の改善点が無視されているのは
# どうしてなんでしょうか。理解されたのかどうかも不明ですね。

【76347】Re:2つの値検索して取得
お礼  you  - 14/11/1(土) 22:04 -

引用なし
パスワード
   ▼γ さん:
ありがとうございました。
作っていただいたマクロでうまくいきました。
原因は名のところが別セルの部分から関数で入って
いたため文字列として認識していませんでした。

いろいろと考えてくださって、本当に助かりました。

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