Excel 行番号を調べる

質問

Excel で違うブックの表から　入力した文字と同じセルを調べ
その隣のセルの文字を表示させたいのですが
（検索するときはブックを開いておいてしてます。）
lookupとかを使ってもなかなか上手く出来ず
vbaを使って表示させようと思い
下のようなスクリプトを作ったのですが
別のブックの表が３万行ぐらいあるので
for next でループさせていたら少し遅いので
もう少し早くならないかと思っています。
machとかでぱっと行番号が解るようにしたいのですが
２日悩んだのですが、出来ませんでした。
で、皆様のお力を借りようと思い。
ご助力を宜しくお願いします。

Function slookup(Pno As Variant, bname As Variant, sname As Variant, Pli As Byte, searchli As Byte) As Variant
Dim slow As Long
Dim I As Long
Dim lrow As Long

On Error GoTo Fault:
lrow = Workbooks(bname).Worksheets(sname).Range("A65536").End(xlUp).Row
For I = 1 To lrow
If Workbooks(bname).Worksheets(sname).Cells(I, Pli) = Pno Then
slow = I
Exit For
End If
Next I

slookup = Workbooks(bname).Worksheets(sname).Cells(slow, searchli)
If slookup = 0 Then slookup = ""
'スクリプトを抜ける
Exit Function
Fault:
slookup = ""
End Function

回答No.4

noname#70958 · ベストアンサー

「最速」というわけではありませんが、
とりあえず「手軽でそこそこ速い」方法として。

●ワークシート関数のMatchを使う

'------------------------↓　ココカラ　↓------------------------
Function slookup2(Pno As Variant, bname As String, sname As String, Pli As Byte, searchli As Byte) As Variant
　Dim slow As Long
　Dim myRng As Range

　On Error GoTo Fault:

　With Workbooks(bname).Worksheets(sname)
　　Set myRng = .Range(.Cells(1, Pli), .Cells(65536, Pli).End(xlUp))
　　slow = Application.Match(Pno, myRng, 0)
　　slookup2 = .Cells(slow, searchli)
　End With

　Exit Function
Fault:
　slookup2 = ""
End Function
'------------------------↑　ココマデ　↑------------------------

※ダミーデータを用いた実測ではオリジナルの60倍超のスピードで動作します。
※Pnoは数値か文字列か判りませんからVariant型で良いのですが、
　bname,snameはVariant型で取ると、
　　=slookup2(A2,$B$1,$C$1,1,2)
　のようにブック名やシート名をセル参照する使い方ができないので不便かと思い、
　String型に直しました。

Excel2000で動作確認。以上ご参考まで。

お礼

おぉ、充分早いです。
ありがとうございます。
実は、Match私もやってみようとしたのですが、
Rangeの指定が間違ってました。
ありがとうございます。

回答No.3

lul

確かにFunctionでFindが上手く動かないですね…。
試しにFindの部分だけSubにしてFunctionからcallしてみましたがそれでもダメでした。。。
ところでうちの環境もOffice2000ですが、VLOOKUP使えましたよ。

=VLOOKUP(A1,[Book1.xls]Sheet1!$A$1:$B$12,2,TRUE)

こんな感じで値を取得できました。
これでもやっぱり出来ませんか？

補足

VLOOKUP　他のブックでも使えました
でも、VLOOKUP関数って検索する列の
順番が正順でないと、ちゃんと検索してくれないみたいで
やっぱりマクロでやってみます。

回答No.2

lul

VLOOKUP関数だとご希望通りの事が出来ると思いますがどうですか？
VBAでやるよりはお手軽ですよ。
"=VLOOKUP(検索値,範囲,列番号,検索の型)"

補足

VLOOKUPでやってみたのですが
同じブック内なら出来るのですが
他のブックになるとエラーが出るのですが
やっぱりＥｘｃｅｌが古いからかな～
今のは出来るのでしょうか？

回答No.1

phoenix343

VBAでやるんだったら
Findメソッド使うほうが早いし、よほどラクだよ？

Excel VBA 入門講座 (Find)
http://excelvba.pc-users.net/fol7/7_1.html

補足

やってみたのですが、
subプロシージャだと出来るのですが
Functionプロシージャだと出来ないのですが
私のＰＣだけでしょうか？
Ｅｘｃｅｌ２０００だからかな～？？