エクセルVBAで関数が入った空白セルの取得

質問

エクセルVBAで下記のようなセルの参照をしたいのですが、よろしくお願いします。（エクセル２００２です）

　　A　　B　　C　　D　　E　　F
１　78　80

セル（A1）（B1）には値も関数も入れられています。
セル（C1）（D1）（E1）には値は入っていませんが、ワークシート関数が組み込まれており空白となっています。（関数の""にて空白）
セル（F1）には何も入力されていません。

今回はセル（B1）の80の値を取得できるようにVBAで下記のようにコードを組みました。

Range（"F1").End(elToLeft).Activate

そうすると、セル（E1）に関数があるために（？）（E1）がアクティブな状態となります。

関数が入力されていても空白として（B1）をアクティブな状態にするために良い方法はありますでしょうか？

誠に申し訳ございませんが、よろしくお願いします。

回答No.4

papayuka · ベストアンサー

#1です。

1:1 は 1行目全部。

Match関数+MAX関数で数値の入っている最終列を出して、Index関数でその列番号の値を取ってきているって感じです。

元ネタはこちら。
元ネタの A:A は A列全部。
http://www2.odn.ne.jp/excel/waza/function.html#SEC63

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html#SEC63

お礼

papayuka様、本当にありがとうございました。大変助かりました。
まだまだ、初心者でついつい難しくやってしまうところでした。

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

回答No.3

papayuka

#1です。

引数無しでもそのままでExcel2000では動きました。
ただ、 Application.Volatile を入れないと再計算されないけど。

でも、ユーザー定義関数を使わなくても下記の関数を A2 に入れるのと同じような、、、

=INDEX(1:1,MATCH(MAX(1:1)+1,1:1,1))

補足

papayuka様ありがとうございます。ワークシート関数で処理できればもちろん一番でした。ありがとうございました。

しかし、ずっと考えていたのですが、上記の関数全く理解できず・・・・情けない。。。

もし、お時間ございました解説していただけると大変助かります。。。いろいろ試したのですが、まず1:1からわかりません。

参考となるHPだけでも教えていただけたら
助かります。よろしくお願いします。

回答No.2

imogasi

ユーザー関数にしてみました。
Function leftd(a)
c = a.Column
For j = c - 1 To 1 Step -1
If Cells(a.Row, j) = "" Then
Else
leftd = Cells(a.Row, j)
Exit Function
End If
Next j
End Function
=leftd(F1)のようにセルにいれます。A-E列で一番右終りのデータを返します。
関数化しないなら、ロジックを参考にしてください。

補足

imogasi様早速のご回答ありがとうございます。
＃１様の補足欄の通りに今回はユーザー定義関数を組みました。

imogasi様のロジックを考えていたのですが、まだ、初心者な者でいまいち理解できません。申し訳ございません。

今回は一番後ろのF列からA列にかけて入力された最後の数字を探し、他のセルに代入したいのですがご指導いただけますでしょうか？どうかよろしくお願いします。

ちなみに、imogasi様のロジックのCとかは、変数の宣言とかは不要なのでしょうか？？？

回答No.1

papayuka

関数が""以外の場合はどうしたいのでしょう？
C1<>"" だったら C1、D1<>"" だったら D1 のようにしたい？

ループくらいしか思い浮かびませんでした。

Sub Test()
Dim r As Range, sr As Range
　Set sr = Range("A1")
　For Each r In Range("A1:E1")
　　If r.Value <> "" Then Set sr = r
　Next r
　MsgBox sr.Address & " = " & sr.Value
　sr.Activate
End Sub

補足

papayuka様、早速のご回答ありがとうございます。
また、質問の言葉足らずをお詫び申し上げます。

関数が""以外の場合、ユーザー定義関数でその値を他のセルに代入します。

　　A　　B　　C　　D　　E　　F
１　78　80
２

（A2)に（B2)の８０を入れたいのです。
functionプロシージャで、関数名を消費水準として
ループで次のようにコードを組みました。

Function 消費水準() As Long

Dim i As Integer
i = 6
Do While Cells(1, i) = ""
i = i - 1
Loop

消費水準 = Cells(1, i).Value

End Function

そして、セル（A2)にユーザー定義関数で
＝消費水準（）
とした場合は引数がないのでVALUE！になってしまいます。引数を設定しない場合のユーザー定義関数はどうすれば良いのでしょうか？

何度もお手数をお掛けし申し訳ございません。よろしくお願いします。