エクセル2007 参照セルの値が認識されない

質問

エクセル2007の環境で下記マクロを実行すると、
アクティブシートのセルA1に数字が入っている場合、Sheet1のセルA1に値を転記しても
そのセルA1の値をVLOOKUP関数で参照できません。

マクロに問題があるためなのか何処に問題があるのか分からないので教えてください。
宜しくお願いいたします。

【sheet】は
アクティブシートの3行目から1000行目までのA列のセルをクリックしたらセルA1に値を表示

その後、下記【モジュール】を使用し、
アクティブシートのセルA1が空白の場合、
A列の数値をSheet1のセルA1に転記し、アクティブシートのA列の数値が空白になるまで循環する。
Sheet1のセルA1の値をVLOOKUP関数で参照した内容を表示、印刷します。

-----------------------------------------------------------------
'【sheet】
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target
If .Count > 1 + IsEmpty(.Value) Then Exit Sub
If Application.Intersect(.Cells, Range("A3:A1000")) Is Nothing Then Exit Sub
ActiveSheet.Cells(1, 1).Value = .Value
End With
End Sub
-----------------------------------------------------------------
'【モジュール】
Sub TEST()
Dim myBtn As Integer
Dim myMsg As String, myTitle As String
Dim WS1 As Worksheet, WS2 As Worksheet
Dim i As Long

myMsg = "●●●"
myTitle = "確　認"
myBtn = MsgBox(myMsg, vbYesNo + vbExclamation, myTitle)

If myBtn = vbYes Then

Set WS1 = ActiveSheet
Set WS2 = Sheet1

If WS1.Cells(1, 1).Value = "" Then

With WS1
For i = 3 To 65536
If .Cells(i, 1).Value = "" Then Exit For
WS2.Cells(1, 1).Value = .Cells(i, 1).Value
WS2.Cells(1, 2).Value = ActiveSheet.Name

'WS2.PrintOut Copies:=1
Next i
End With

ElseIf WS1.Cells(1, 1).Value >= 1 Then

WS2.Cells(1, 1).Value = WS1.Cells(1, 1).Value
WS2.Cells(1, 2).Value = WS1.Name
'WS2.PrintOut Copies:=1

Else
End If
End If
End Sub
-----------------------------------------------------------------

回答No.2

tsubuyuki · ベストアンサー

コードを拝見すると
・Sheet2のＡ１セルが空白だったら
　　　　・Sheet2の３行目以下の各行に対し
　　　　・Ａ列の値をSheet1のＡ１セルに転記
　　　　・Sheet2の名前をSheet1のＢ１セルに入力
　　　　・Sheet1を印刷（現状はコメント化＝無効にしているようですが）
　　　　・Sheet2の全行に対して繰り返し（Ａ列が空白の行は無視）
・SHeet2のＡ１セルが空白じゃなかったら
　　　　・Sheet2のＡ１セルの値を Sheet1のＡ１セルに転記
　　　　・Sheet2の名前をSheet1のＢ１セルに入力
　　　　・Sheet1を印刷（現状は・・以下同文）
の一連の動作を行いたいので、マクロが必要と言うことのようで。

まず、つっこんでおきたいのが・・
「ＶＬＯＯＫＵＰの要素はどこにあるのでしょう？」と言うこと。
コードを拝見する限り、その要素は「ここには一つも無さそう」です。

Sheet1のＡ１に転記しても
「どこかのセルに仕込んであるＶＬＯＯＫＵＰ関数が反応しない」
と言うのであればこの式の見直しが必要ですが、
質問文中からはそれらの情報が読み取れません。

なので、「回答しようがない質問」と言わざるを選えません。

とまぁ、それだけでは何となく申し訳ないので、
本来は「他人さまのコードの添削」は趣味ではないのですが、
個人的な好みも含めて、思ったことをいくつか。

> For i = 3 To 65536
> If .Cells(i, 1).Value = "" Then Exit For
現状だと、Ａ列が空白でも空白じゃなくても６５５３４回、
このチェックをしてしまいますので、処理時間的に無駄です。
なので「Ａ列を基準に最終行まで」と言う書き方、
　　　　For i = 3 To WS1.Cells(Rows.Count, 1).End(xlUp).Row
こんな書き方をオススメします。
この場合は「途中にＡ列が空白の行を挟まない限り」、If以下は不要です。

> Set WS1 = ActiveSheet
> （略）
> With WS1
> (略）
> WS2.Cells(1, 2).Value = ActiveSheet.Name
> （略）
> End With
せっかく、変数WS1を定義してWithで括っているのですから、
　　　　WS2.Cells(1, 2).Value = .Name
で良いですね。
（そもそも、Withが必要かどうか？はとりあえず言及しません。）

> If WS1.Cells(1, 1).Value = "" Then
> （略）
> ElseIf WS1.Cells(1, 1).Value >= 1 Then
> （略）
> Else
> End If
空白か空白じゃないか、と言うIf分岐ですから、
　　　　If WS1.Cells(1, 1).Value = "" Then　'空白の場合
　　　　（略）
　　　　Else　　　　'そうじゃない（空白じゃない）場合
　　　　（略）
　　　　End If
で、おそらく良いと思います。

とりあえず、以上、参考までに。

※「どこかのセルに仕込んであるＶＬＯＯＫＵＰ関数が反応しない」
　に該当するのであれば、この質問は一度クローズして
　現状・目標・現象などの詳細を明記した上で別に質問を立てることをオススメします。

お礼

>=VLOOKUP($A$1,INDIRECT(B1),3,FALSE)
VLOOKUP関数の初歩を忘れてました。
検索の型をFALSEからTRUEにすることで
問題を解決することができました。
ありがとうございました。

補足

> For i = 3 To 65536
> If .Cells(i, 1).Value = "" Then Exit For
早速、ご指摘していただきました
For i = 3 To WS1.Cells(Rows.Count, 1).End(xlUp).Row
に変更してみました。とても勉強になります。
ありがとうございます。
VLOOKUP関数については、補足説明を入れましたので、
よろしくお願いいたします。

回答No.1

KURUMITO

そのセルA1の値をVLOOKUP関数で参照できません。
シート１のA１セルの値を用いてVLOOKUP関数でどのシートのどの列の何行目のデータをどこのセルに表示させようとしているのでしょう。
シート１のA１セルの値を使ってシート２のA列で該当する行を求め、例えばそのB列の値をVLOOKUP関数で表示させようとしているとしたらシート２のB１セルの値を表示させることになっておそらく空白のセルになってしまうでしょう。
どうしてマクロを使ってそのような操作をしようとしているのか理解できませんね。

補足

説明が漏れていましたので、追記します。
申し訳ございませんでした。

'【モジュール2】
Sub 名前の定義()
i = ActiveSheet.Name
ActiveWorkbook.Names.Add Name:=i, RefersToR1C1:="=C1:C14"
End Sub

'【モジュール】
Sub TEST()
省略・・・
If myBtn = vbYes Then
名前の定義
省略・・・
End sub

ボタンを押した際に、アクティブシートに対して、名前の定義を作成し、
'【Sheet1】内の任意のセル（たとえば、セルA2）に下記の関数を入れます。
=VLOOKUP($A$1,INDIRECT(B1),3,FALSE)

しかし、
>・SHeet2のＡ１セルが空白じゃなかったら
>　　　　・Sheet2のＡ１セルの値を Sheet1のＡ１セルに転記
>　　　　・Sheet2の名前をSheet1のＢ１セルに入力
>　　　　・Sheet1を印刷（現状は・・以下同文）
空白じゃなかった場合、・Sheet2のＡ１セルの値を Sheet1のＡ１セルに転記はできるのですが上記のVLOOKUP関数が反映されません。

空白の場合は、VLOOKUP関数が反映されるので、理由がわかりません。
よろしくお願いいたします。