別ブックの最終行の数値を式に取り入れたい

質問

Book1　Sheet1　A列に製造番号を縦に連番で入力して保管しております。

Book2　SheetA　A1に、Book1　Sheet1の最終行番号+1を求め
製造番号を自動作成したいと考えております。

Book１は製造番号のデータ蓄積として保管していくつもりですので
最終行は固定されません。
マクロの記録ではEnd(xlUp).を行っても

ActiveCell.FormulaR1C1 = "=Book１.xls!R[150]C1+1"

と表現され、これでは最終行にはならなくて、困っております。
どうすれば良いか御教授下さい。

回答No.1

MackyNo1 · ベストアンサー

このようなケースでは以下のような関数で対応するほうが簡便です。

=LOOKUP(10^10,[Book1.xls]シート名!$A:$A)+1

お礼

ご回答ありがとうございます。

補足

申し訳ございませんが、私の勉強不足か
アドバイス戴いたLookup関数の検査値の内容がつかめません。
どういう意味でしょうか？
ご多忙中とは存じますが、詳しくお願い申し上げます。

回答No.6

imogasi

判りにくい放言になっている。
＞Book2　SheetA　A1に、Book1　Sheet1の最終行番号+1を求め、製造番号を自動作成したいと考えております
自動作成とは何？
Book1の最終番号の次をSheet2にせっとしたいのか。
MAX関数とか、最終行をとらえる後述のコードで出来る。
最大はVBAにはMAX関数が無いのでApplication.WorksheetFunctuion。Max(・・・)を使うことになると思う。
しかし、文字列数字番号だと難しいかも。
＞Book2　SheetA　A1に、Book1　Sheet1の最終行番号
は下の方からEnd(xlUp）にあたる操作をかければ良い。ENDキーと上矢印キー。
この質問にはエクセルバージョンが書いてないよ。２００３までならA65536から上に向けてのコードを書いているが、１万行もないならA10000から上に操作しても良い。
マクロの記録をやればわかることだよ。
Sub Macro3()
Range("A10000").Select
Selection.End(xlUp).Select
End Sub
これをまとめて
Sub Macro3()
d= Range("A10000")..End(xlUp).Row
End Sub
＝＝
ただしブック＋シートを区別して書かないとエラーになるかも。　　別ブックの２つのシートを扱うコードこそ質問者が質問すべきことと思う。
VBAを習いはじめて直ぐに、２つのブックや２つのシートを扱うのは早すぎる。

補足

申し訳ありません。
Excelのバージョンは2003です。

確かに解り難い稚拙な説明でした。
Aに製造番号
Bに受注商品名
Cに色

Aは5000行までコピーしており、
BとCに受注した商品の情報を別ブック、別シートからコピーペーストし、
A列の、BとCの最終行の1行下を削除したい
という事です。

回答No.5

MackyNo1

>誠に申し訳ないのですが
結果が“＃N/A”となってしまいました。

データ範囲に1つも数字データが無いのではないでしょうか？

＞セルの書式設定を確認いたしましたが、
ユーザー定義0000と設定していたのですが
なぜかその他（ロシア語）となっておりました。

数式が正しくないこととは全く別の問題です。
他ブックでなくともよいので、きちんと数値が入力されたセル（表示形式は関係ない）を対象にしたLOOKUP関数を入力して、ご希望の最下行データが返ることを確認してください。

＞ちなみにLookUp関数ですが、Book1に製造番号以外の情報を
今後追加で入れるとなると、Vlookup関数の方が安全でしょうか?
例えば、製造した物の金額をB列に入力していると
金額が優先されるのでしょうか?

一般にVLOOKUP関数は、特定の検索値に該当する他の列のデータを検索する関数で、同じ範囲の中の最下行のデータを取得するのに使用するのは不適切です（使えない訳ではない）。

また、LOOKUP関数で数値を検索した場合は、文字列は無視されるので、一番下の数値データがヒットします。

ところで、A列の製造番号の質問に、どうしてB列の金額が関係するのでしょうか？

お礼

何度もご回答ありがとうございます。
稚拙な説明で申し訳ありませんでした。

補足

下記の様なマクロを組んでいます。

Range("A1").Select
Selection.NumberFormatLocal = "0000"

'製造番号の取得
Workbooks.Open ("\\C\\SCM\DATE\BKUP\Book2.xls")
Workbooks("Book1").Activate
Sheets("Sheet1").Activate

Sheets("Sheet1").Range("A1") _
= Workbooks("Book2.xls").Sheets("SheetA").Cells(Rows.Count, 1).End(xlUp).Row

Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2"), Type:=xlFillDefault
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A5000"), Type:=xlFillDefault
Range("A2:A5000").Select

＞ところで、A列の製造番号の質問に、どうしてB列の金額が関係するのでしょうか？

MAXで検索すると、4桁の製造番号より大きい金額の場合、数値として金額をヒットさせるのでは無いのかと考えたからでした。

セルのユーザー定義で“0000”にすると（ロシアではありませんでした）
ポルトガルの郵便番号文字列扱いになっているようですので、
文字列が無視されるLOOKUP関数は使えない様です。

回答No.4

silverfd

値じゃなくて、行数ですよね。
#1の方の数式の”LOOKUP”を”MATCH”にすれば出来ます。
無論#3の方のマクロでもOKですね。

お礼

ご回答ありがとうございます。
早速試してみます。

現在の所、数値でしかないので、行数でも大丈夫な気がしますが
今後アルファベットが入った場合や、使用しない製造番号を
使わなければならない場合、運用でカバー出来るのでしょうか?

回答No.3

mar00

Book１を開いているという事が前提ですが
Sub Macro1()
Sheets("SheetA").Range("A1") = Workbooks("Book1.xls").Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
End Sub

お礼

ご回答ありがとうございます。

行数をカウントするということでしょうか?
見出し行を追加した場合、+1を無くせば良いのでしょうか?
試してみます。

回答No.2

MackyNo1

>アドバイス戴いたLookup関数の検査値の内容がつかめません。
どういう意味でしょうか？

「10^10」は10の10乗という意味で製造番号よりも大きな数字ならどのような数字でもかまいません。

LOOKUP数式の意味はデータ範囲の中から１０の10乗という数字を探しますが、この値以上の値が無いので、最後のデータ（一番下のセル）がヒットすることになります。

お礼

早速のご回答ありがとうございます。

補足

誠に申し訳ないのですが
結果が“＃N/A”となってしまいました。

セルの書式設定を確認いたしましたが、
ユーザー定義0000と設定していたのですが
なぜかその他（ロシア語）となっておりました。

何が起きたのか解りません。
何度も書式設定を変更しても入力する度に
その他になっております。

ちなみにLookUp関数ですが、Book1に製造番号以外の情報を
今後追加で入れるとなると、Vlookup関数の方が安全でしょうか?
例えば、製造した物の金額をB列に入力していると
金額が優先されるのでしょうか?