みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

【Excel VBA】データの最終行について

Excel2003を使用しています。

ある一覧表形式のデータSheet1をSheet2に値のみコピーして、このSheet2を“印刷用”として、ページ設定等をして、印刷のみに使用しようと思っています。

Sheet2のI列、J列、K列には数値が入力されていて、I列、J列、K列のデータの最終行の1行下に、それぞれ6行目からデータ最終行までの合計の数式が入力されるよう、コードを追加したのですが、数式は入力されるものの、入力したい行に数式が入力されません。

マクロを実行して、数式が入力された行を見てみると、301行目に入力されていました。
Sheet1は別のシートのデータを数式により表示していて、数式が300行まで入力されているので、Sheet2の元になっているSheet1の影響(?)なのかな~?と…。こういう場合、どうすればいいでしょうか?
コードは下記のようになっています。
よろしくお願いします。

----------------------------------------

Sub 印刷用作成()
Dim i As Integer
Dim j As Long

Sheets("Sheet1").Activate
Range("B6:L6", Range("L6").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False

For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub

投稿日時 - 2008-05-16 17:24:30

QNo.4026873

すぐに回答ほしいです

質問者が選んだベストアンサー

>Sheet1は別のシートのデータを数式により表示していて
>数式が300行まで入力されているので、Sheet2の元に
>なっているSheet1の影響(?)なのかな~?と…

その通りです。
I,J,K列以外にどんな値が入っているのか提示されてないのでなんですが。。。

ふつうこういった表には、他の列(B、C。。)に項目(得意先名など)が入っているはずなんですが、
それらはどうなってますか?
I,J,K列以外で表の最終行はとれないのですか?

例えば、B列で表の最終行が取れるとして。。。

'-------------------------------------------------
Sub 印刷用作成()

 Dim i As Integer
 Dim LastRow As Long
 Dim myRange As Range

 Sheets("Sheet1").Activate
   LastRow = Cells(65536, "B").End(xlUp).Row
   Range("B6:L" & LastRow).Copy
 Sheets("Sheet2").Activate
   Range("B6").PasteSpecial xlPasteValues
 Application.CutCopyMode = False

 For i = 9 To 11
   Set myRange = Range(Cells(6, i), Cells(LastRow, i))
   Cells(LastRow + 1, i).Value = WorksheetFunction.Sum(myRange)
 Next i

End Sub
'-------------------------------------------------

以上。
 

投稿日時 - 2008-05-16 18:49:06

お礼

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

>I,J,K列以外で表の最終行はとれないのですか?

Sheet2のデータは全てSheet1から数式で表示しているので、Sheet2からはデータの最終行を取得できませんでしたが、大元のSheetを利用することで、データの最終行を取得できたので、これを元にうまく処理することができました。
ありがとうございました。

投稿日時 - 2008-05-19 11:36:06

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

こんばんは。

この部分は、ユニークですね。こういう方法もよいですね。

For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next

シート2のI列から3列の最終行の次の行を決めるためですが、ただ、コード自体が、シート・モジュールに書いてしまっているかもしれません。

Sheet1 のシートモジュール(ローカル)に書くと、Cells の親オブジェクトは、いくら、Sheetを変えても、何も上位オブジェクトを書かないと、Sheet1 の場所を探してしまいます。
Excelの場合は、特殊な場合を除いて、標準モジュールを使います。

あえて、シートモジュールに書くためには、親オブジェクトを意識して、例えば、以下のように、With ステートメントを入れてみたらいかがでしょうか。そうすると、狂いが少なくなります。(中に書くのは、.Range, .Cells というように、オブジェクト等の前に「.(カンマ)」が付きます。)


Sheets("Sheet2").Select 'あってもなくてもよい
With Worksheets("Sheet2") '←With ステートメントを加える
 .Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False

For i = 9 To 11
j = Application.Max(j, .Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
 .Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub

End With

-----------------------------
'私案:(標準モジュール、またはシートモジュール)

Sub 印刷用作成2()
  Dim i As Integer
  Dim j As Long
  Dim r As Range
  With Worksheets("Sheet1")
    Set r = .Range("B6", .Range("L6").End(xlDown))
    Worksheets("Sheet2").Range("B6").Resize(r.Rows.Count, r.Columns.Count).Value _
     = r.Value '値コピーの場合
  End With
  With Worksheets("Sheet2")
    For i = 9 To 11
      If j <= (.Cells(65536, i).End(xlUp).Row + 1) Then
        j = .Cells(65536, i).End(xlUp).Row + 1
      End If
    Next
    If j > 1 Then
      .Cells(j, 9).Resize(, 3).FormulaLocal = "=SUM(R2C:R[-1]C)"
    End If
  End With
  Set r = Nothing
End Sub
  

投稿日時 - 2008-05-17 00:22:16

お礼

Wendy02 さん、こんにちは。
いつもありがとうございます。

>コード自体が、シート・モジュールに書いてしまっているかもしれません。

コードは標準モジュールに記述していますが、やはり、Sheet2の元になっているSheet1の影響のようです。
Sheet2のデータは全てSheet1から数式で表示しているので、そのSheet1の元になっているSheetを利用して、データの最終行を求めることができました。

同様の処理をするコードでも、いろんな書き方があるのですね。
勉強になります。ありがとうございました。

投稿日時 - 2008-05-19 11:46:16

ANo.1

Sheet2の、(65536,9)、(65536,10)、(65536,11)それぞれで、そのセルをアクティブにした状態から Ctrl+↑で、どのセルにジャンプするか試してみましょう。

投稿日時 - 2008-05-16 17:42:10

お礼

アドバイスありがとうございます。

Ctrl + ↑で、どのセルにジャンプするかで調べることができるのですね。大変参考になりました。
やはり、Sheet2の元になっているSheet1の影響で、数式を入力したい行に入力できなかったようです。

今回は、ANo.2の方からアドバイスをいただいた“別の列からデータ最終行を取得する”方法で、うまくいきました。
ありがとうございました。

投稿日時 - 2008-05-19 11:24:49

あなたにオススメの質問