[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Resizeの範囲指定方法』（VBA初心者）
VBAの初心者です。
先日、別の問題を質問し、皆様のおかげで無事解決したのですが、
また新たな問題に直面しています。
下記のようなコードを書きたいのですが、
Resizeの範囲指定の方法が分からず、悪戦苦闘しています。
解決方法をご教示頂きたくお願いいたします。
Sub Input1()
Dim ws As Worksheet
Dim c As Range
For Each ws In Worksheets
If ws.Name <> "マスタ" Then
Set c = ws.Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn
Worksheets("マスタ").Range("C39").Copy
c.Offset(2, -7).Resize(Rows.Count, ).PasteSpecial Paste:=xlPasteValues
Worksheets("マスタ").Range("C41").Copy
c.Offset(2, -6).Resize(Rows.Count, ).PasteSpecial Paste:=xlPasteFormulas
End If
Next
End Sub
＞＞c.Offset(2, -7).Resize(Rows.Count, ).PasteSpecial Paste:=xlPasteValues
まずこの部分で、実行エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーと表示されてストップします。
マスタ以外の複数のシートの指定範囲へ、
マスタシートの２つの指定セルのデータをコピーすることが目的です。
各シートの最終列から左側へ指定列数分移動し、
行を下側へ指定行数分移動した箇所を起点として、
その列の最終行まで範囲選択し、
マスタシートのセルのデータをペーストします。
値と数式のデータです。
但し、複数のシートの最終列は、全シート毎に一定数増加していきますが、
最終行はそれぞれのシート毎にバラバラに増加していく為、
一定ではありません。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
（もこな２） 2021/08/08(日) 21:21
ws.Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn.Offset(2, -7).Resize(Rows.Count)
~~~~~↑~~~~~ ~↑~
これは正しいですか？
（もこな２） 2021/08/08(日) 21:27
Resize(Rows.Count, ) → 選択範囲の変更(最終行まで範囲指定, 列範囲指定無し）、
という形になるのかと考えておりましたが、違うのでしょうか。。。？
ws.Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn.Offset(2, -7).Resize(Rows.Count)
→ 試してみたところ動かなかったので、恐らく間違いなのかと思いますが、、、
１）4行目の最終列から、任意の列まで移動し、
Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn.?
２）指定列の任意の行まで移動し、
Offset(2, -7)?
３）指定セルから最終行まで選択、
？？？
４）C?Bへ指定データを貼り付け。
？？？
（VBA初心者） 2021/08/08(日) 22:54
entirecolumnは列全体を指すので、オフセットで行位置を上下させるような動かし方はできないかと
やり続ければ、すぐ上達すると思います
がんばってください
（観覧車） 2021/08/09(月) 00:46
Dim Rng As Range
Set Rng = Range("A1").Resize(Rows.Count) '---(A)
Set Rng = Range("A2").Resize(Rows.Count) '---(B)
(A)は実行時エラーにならないが、(B)は実行時エラーになる。
なぜかといえば、ワークシートの最大行数を超えたセル範囲だから
（傍観者） 2021/08/09(月) 05:23
■１
ヒントで書いたように「Rows.Count」は「ActiveSheeet.Rows.Count」と解釈されてます。
すなわち、Office365なら「1048576」となりますね。
■２
Rangeオブジェクト.Resize（行数,列数）
↑は、セル（範囲）を、指定した行数、列数に拡張（縮小）する命令です。
この時、行数（列数）を省略すると、そのまま変更しないの意味になります。
したがって、例えば↓のようになります。
Range("A1:B3").Resize(4,1)･･････････A1:A4
Range("A1:B3").Resize(4,)･･･････････A1:B4
したがって「■1」を踏まえて考えれば、例えば Range("A4").Resize(1048576)としたらシートの外に3行分あふれちゃいますよね？
なので、「.Resize(Rows.Count, )」で本当に正しいのか確認しました。
■３
次に↓について考えてみましょう
ws . Cells(4, ws.Columns.Count).End(xlToLeft) . EntireColumn
wsシート の XDF4セルから左方向にみて最初にデータのあるセル の 列全体
という意味になっています。
これを踏まえて、例えば↓がどうなるか考えてみましょう。
Range("H:H").Offset(2, -7)
列のほうは７列左へずらすからA列ということになり、問題ないですね。
しかし行のほうは、列全体を2行下げることはできませんよね？（２行はみ出しちゃうので）
ということを踏まえると↓は
ws.Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn.Offset(2, -7).Resize(Rows.Count)
・「列全体を2行下げろ」という命令に対して、Excel君がそんなことできないとエラーを出している ・【列】全体の【行】をResize(1048576)にしようとしてるので、命令は実行できるが意味はない
というところが問題であろうと思われます。
■４
この質問は↓の続きですよね。
[[20210807131746]] 『都度変化する最終列から指定列までのコピーと挿入』（VBA初心者）
↑のまっつわんさんのコードをよく研究されるべきだと思います。例えば↓に着目してみましょう。
ws.Cells(4, ws.Columns.Count).End(xlToLeft).EntireColumn.Offset(, -7).Resize(, 4).Copy
↑について、「ws.Cells(4, ws.Columns.Count).End(xlToLeft)」がで得られるセルが【H4セル】だとしたら↓のようになります。
ws.Range("H4").EntireColumn .Offset(, -7) .Resize(, 4) .Copy
H列全体 7列右にずらして 行はそのまま4列に拡張して コピーしなさい
すなわち↓のような意味でした。
ws.Range("A:D").Copy
■５
しかし、今回のケースでは↓のような条件ですから列全体の話はではないですよね。したがって「EntireColumn」のことは忘れましょう。
各シートの最終列から左側へ指定列数分移動し、 行を下側へ指定行数分移動した箇所を起点として、 その列の最終行まで範囲選択し、 マスタシートのセルのデータをペーストします。 値と数式のデータです。
さて、条件を順番にみていくと
各シート → ループで回した時の 「ws」 最終列 →「.Cells(4, ws.Columns.Count)」 から左側へ指定列数（7列）分移動→「.Offset(, -7)」
ここまでは大体理解できているとおもいますが、↓の対応ができていません。
箇所を起点として、その列の最終行まで
ここで、最終行の求め方について考えてみましょう。
列のほうは【最大列】から【左】に見ていってデータのある個所を調べたのですから、同じ理屈で【最大行】から【上】に見ていけば、目的のセルにたどり着くのは理解できますよね？
これをコードにするとこんな感じです
ws.Cells( ws.Rows.Count, ws.Cells(4, ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column) .Offset(, -7) .End(Xlup)
wsの最大行 wsのXDF4セルから左に見てデータのある最初のセルの列 から左に7列ずらしたセル から【上】に見てデータのある最初のセル
ちょっとわかりづらいので分解するとこうです。
Sub 研究01()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim 行 As Long, 列 As Long
列 = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Offset(, -7).Column
行 = ws.Cells(ws.Rows.Count, 列).End(xlUp).Row
MsgBox "得られたセルは " & ws.Cells(行, 列).Address(0, 0) & " です"
End Sub
このようにすれば、指定した列の【最終行】を求めることができることがわかりますね。
■６
さて、「■５」までで、コピー対象となるセル範囲のうち、最初のセルと最後のセルの求め方は分かったと思います。
では、【セル範囲】をExcel君に伝えるのはどうしたらよいかという話になります。
方法はいくつかありますが、Resizeを使うなら、起点をベースに何行、何列に拡張すればよいかを考えます。
今回のケースで考えると起点が4行目なっています。つまり既に３行分下に下がったセルからスタートしているわけですから、その分は引かなければ正しくありません。
よって、こんな感じになるでしょう。
Sub 研究02()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim 行 As Long, 列 As Long
列 = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Offset(, -7).Column
行 = ws.Cells(ws.Rows.Count, 列).End(xlUp).Row
MsgBox "得られたセル範囲は " & ws.Cells(4, 列).Resize(行 - 3).Address(0, 0) & " です"
End Sub
また、Resizeを使わずともApplication.Range（起点セル,終点セル）のように指定することでもセル範囲を表現することが可能です。
Sub 研究03()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim 行 As Long, 列 As Long
列 = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Offset(, -7).Column
行 = ws.Cells(ws.Rows.Count, 列).End(xlUp).Row
MsgBox "得られたセル範囲は " & Application.Range(ws.Cells(4, 列), ws.Cells(行, 列)).Address(0, 0) & " です"
End Sub
なお、いずれの場合も行が4未満になるときは、データがないということですから処理してはダメですよね。
■７
ということを踏まえるとこんな風に修正すればよいでしょう。
Sub Input1_修正1()
Dim ws As Worksheet
Dim 列 As Long, 行 As Long
For Each ws In Worksheets
If ws.Name <> "マスタ" Then
列 = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Offset(, -7).Column
行 = ws.Cells(ws.Rows.Count, 列).End(xlUp).Row
If 行 >= 4 Then
Worksheets("マスタ").Range("C39").Copy
ws.Cells(4, 列).Resize(行 - 3).PasteSpecial Paste:=xlPasteValues
Worksheets("マスタ").Range("C41").Copy
ws.Cells(4, 列).Resize(行 - 3).PasteSpecial Paste:=xlPasteFormulas
End If
End If
Next ws
End Sub
ただし、このままではC39セルの値貼付は、C41の数式貼付で上書きされるので意味がないですね。
本当はどのようにしたいのか、こちらではわかりませんのでぜひご自身で修正してみてください。
（わからなけれれば、修正後のコードを提示したうえで、○○になるはずが××になるというように状況を説明するとアドバイスが得られると思います。）
（もこな２） 2021/08/09(月) 18:08
誤字訂正しました。
（もこな２） 2021/08/09(月) 20:26
例えばB2セルに色を塗っておいて、
range("B2").offset(2,3).select
range("B2").resize(1,5).select
range("B2").EntireColumn.select
とかのコードを書いてどんな動きをするのか自分で確認しなさい。
（vba） 2021/08/09(月) 23:13
>Resizeの範囲指定方法
Offsetする場合は、シートからはみ出す可能性が常にあるから、
Resizeは丁寧にしないとだめですね。
Option Explicit
Sub test()
Dim ws As Worksheet
Dim strFormula1 As String
Dim strFormula2 As String
Dim wsマスタ As Worksheet
Dim Rng As Range
Dim i As Long
'基本となる数式の取得
With Worksheets("マスタ")
strFormula1 = .Range("C39").Formula
strFormula2 = .Range("C41").Formula
Set wsマスタ = Worksheets(.Index)
End With
'シートを巡回して数式を入力
For Each ws In Worksheets
If Not ws Is wsマスタ Then
With ws.UsedRange
i = .Columns.Count - 7
Application.Range(.Cells(6, i), Cells(.Rows.Count, i)).Formula = strFormula1
Application.Range(.Cells(6, i - 1), Cells(.Rows.Count, i - 1)).Formula = strFormula2
End With
End If
Next
End Sub
数式を入れたいならこんな感じです。
セル範囲を指定する言葉をいろいろ覚え、
臨機応変に使い分けましょう。
（まっつわん） 2021/08/11(水) 08:43
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.