EXCEL 最終行に入力するマクロ

質問

マクロ初心者です。

シート”受注書”からシート”受注履歴”に
履歴情報を書き込むマクロを作成しています。
初心者丸出しで恥ずかしいのですが、
下記のように組んでいます。

Sub 受注情報書き込み()
Dim ws01 As Worksheet
Dim ws02 As Worksheet
Set ws01 = Worksheets("受注書")
Set ws02 = Worksheets("受注履歴")
ws02.Activate
' 受注No入力
ws01.Range("C2").Copy
ws02.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
' 受注日入力
ws01.Range("M2").Copy
ws02.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
' 出荷日入力
Sheets("粗利報告書").Range("D3").Copy
ws02.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
・
・
・
この場合、受注書シートが空白の場合、
受注履歴シートも空白になると思うのですが、
次回、履歴を書き込む時に空白を詰めて（最終行に）
入力してしまう事を避けたいです。
空白は残しつつ、一受注を同じ列に入力する為には、
どうしたら良いでしょうか？

回答No.4

fujillin · ベストアンサー

＞一受注を同じ列に入力する為には～
同じ列 → 同じ行　という意味ですよね？

確実に同じ行にするためには、各列で最終行を判定しないで、書き込む行番号を求めたら、全体でその番号を使うようにするとよいです。

書き込む行番号はどうやって求めるか？
必ず空白でない項目があれば、その列の最終行をもとに決めればよいですし、どの項目にも空白の可能性があるなら、#1様のご提案のようにどこかに最終行を控えておくなどの方法が考えられます。

質問文の内容だけから判断すると、「履歴」シートのA～C列の空白行の最大行番号に書き込むという方法でもよさそうなのでそような例を…

Sub test()
Dim ws01 As Worksheet, ws02 As Worksheet
Dim r As Long, c As Integer, tmp As Long
Set ws01 = Worksheets("受注書")
Set ws02 = Worksheets("受注履歴")

For c = 1 To 3　　　　　　　　　　 '　A列からC列までの最終行を捜査
　tmp = ws02.Cells(Rows.Count, c).End(xlUp).Row
　If tmp > r Then r = tmp
Next c

ws02.Cells(r, 1).Value = ws01.Range("C2").Value ' 受注No
ws02.Cells(r, 2).Value = ws01.Range("M2").Value ' 受注日
ws02.Cells(r, 3).Value = Sheets("粗利報告書").Range("D3").Value '出荷日
End Sub

みたいな感じではいかがでしょうか。
なお、手で捜査する場合は「コピー」-「ペースト」になりますが、VBAの場合は、↑例のように直接値を代入することでも可能です。
（1行の処理ですみます。特に今回はPaste:=xlPasteValuesとしていて、書式等のコピーはしていないので同じ結果になります。）

回答No.5

fujillin

No4です。
投稿してから気がつきました。
No4のままでは、最終行に上書きしてしまいますね。

Next c　の行の次に
r = r + 1
を追加してください。

失礼しました。

お礼

有難うございます!!!!!!
完璧にできました。
感動です。
なるほど・・・勉強になる事ばかりです。
丁寧に教えて頂きまして、本当にありがとうございます。

回答No.3

Wendy02

こんにちは。

なかなか難しい質問だと思います。
私の場合は、こういう場合、カスタムプロパティを使います。カスタムプロパティというのは、ファイル-プロパティのユーザー設定のことです。手動で設定してもよいのですが、マクロを作ってみました。

サブルーチンの　CountCheck(ByRef iCount As Long)　で、行数を決めます。
初期値は、1になっています。実際の行は、プロパティのユーザー設定の数値を入れてください。

こんな方法もあるというぐらいでもよいです。

'-----------------------------------------------

Sub 受注情報書き込み()
Dim ws01 As Worksheet
Dim ws02 As Worksheet
Dim i As Long
Set ws01 = Worksheets("受注書")
Set ws02 = Worksheets("受注履歴")

Call CountCheck(i) 'サブルーチン
If i <= 0 Then MsgBox "CustomsProperty がヘンです。", 64: Exit Sub

ws02.Activate
' 受注No入力

ws02.Range("A" & i).Value = ws01.Range("C2").Value

' 受注日入力

ws02.Range("B" & i).Value = ws01.Range("M2").Value

' 出荷日入力

ws02.Range("C" & i).Value = Worksheets("粗利報告書").Range("D3").Value

End Sub
Sub CountCheck(ByRef iCount As Long)
'カスタムプロパティに加算
　iCount = ThisWorkbook.CustomDocumentProperties("カウント").Value
　ThisWorkbook.CustomDocumentProperties("カウント").Value = iCount + 1
End Sub

Sub SettingProperties()
'カスタムプロパティの設定(1回きりです)
Dim cnt As Long
　 cnt = 1　'初期値
　 On Error GoTo ErrHandler
　 With ThisWorkbook.CustomDocumentProperties
　　　　.Add Name:="カウント", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeNumber, _
　　　　　　Value:=cnt
　　End With
　　Exit Sub
ErrHandler:
　If Err.Number < 0 Then
　　 MsgBox "既に登録は終わっています。"
　Else
　　MsgBox Err.Number
　End If
End Sub

お礼

ご回答、ありがとうございます！
ちょっと、難かしく、もっと勉強してから
見直したいと思います。
感謝しております。
ありがとうございました。

回答No.2

sykt1217

>非表示セルの値を変数hogeに格納→Cells(1 , hoge + 1)→出力処理→非表示セルの値+1・・・

すみません。↑なんですが、「Cells(1 , hoge + 1)」は参照する非表示セルの初期値が0・空文字・NULLの何れかを想定しています。
参照する非表示セルの初期値を1とし、出力位置の列アドレスが11である場合は「Cells(1 , hoge)」で問題ないです。

言ってることがわからなかったり、うまくいかない場合は補足要求お願いします。

お礼

なるほど。
別セルにカウント・・・
本当に、勉強になります。
有難うございます！！

回答No.1

sykt1217

VBAではセル上の空白（空文字・NULL）が必要なものか否かを判断することができないので、非表示セルを用意して、そこにカウント数を持っておくのが宜しいかと思います。

【初期出力】
受注履歴シートに出力する→出力正常終了→非表示セルの値+1
【次の出力】
非表示セルの値を変数hogeに格納→Cells(1 , hoge + 1)→出力処理→非表示セルの値+1・・・

みたいな感じでどうでしょう？