Resizeでエラーが出る原因と処置

質問

シート構成

|[A] |[B]
[1] |名前　　|出席回数　　　
[2] |いぬ | 5
[3] |ねこ | 4
[4] |うさぎ | 3
[5] |たぬき | 6
[6] | |
[7] | |
[8] | |
[9] | |
[10]|いぬ | 5
[11]|たぬき | 6

以下のコードでは、
50,60のコードはOKですが
70のコードはNG(アプリケーション定義またはオブジェクト定義のエラーです)です。

どう変更すれば、エラーが無くなりますか　？

Option Explicit

Sub test()
Dim i As Long
Dim r As Range
Dim mb As Long

10 mb = Cells(Rows.Count, "B").End(xlUp).Row
20 i = 2
30 For Each r In Range("B2:B" & mb)
40 If r >= 5 Then
50 Cells(i + 8, "A") = Range(r.Address).Offset(0, -1)
60 Cells(i + 8, "B") = Range(r.Address)

70 Range(r.Address).Resize(, -1).Copy Destination:=Range("D1")
80 Stop
90 i = i + 1
100 End If
110 Next

End Sub

回答No.1

SI299792 · ベストアンサー

Resizeに負数は許されません。もし、２列一度にコピペしたいのであればOffsetと組み合わせます。
70 Range(r.Address).Offset(, -1).Resize(, 2).Copy [D1]
但し、これだと全部D1にコピペされるので、上書きされます。

-1になっている所を見ると、Ａ列だけかもしれません。であれば
70 Range(r.Address).Offset(, -1).Copy [D1]

具体的に何をしたいのかわからないので、回答はここまでです。

お礼

SI299792さん、早速解答いただきありがとうございます。

おかげさまで以下で処理できました。
Range(r.Address).Offset(, -1).Resize(, 2).Copy Destination:=Cells(i, "D")

Resizeのパラメターにマイナスは受け付けないのですね。
Offsetと同じ感覚で考えていたので全く気が付きませんでした。

＞具体的に何をしたいのかわからないので

Sub test()で抽出をoffestで行いました
（ヒットしたA列とB列）

抽出するのは同じ行のA列とB列なので
Sub Test()と同じことをResizeを使ってD列に抽出出来ないかと思ったわけです。
（offestなら５０，６０の2行のコードがResizeならSub2()の５０の１行のコードになる）
（D列への抽出は、テスト目的なのでD列に固執するわけではありません。）

Option Explicit

Sub test()
Dim i As Long
Dim r As Range
Dim mb As Long

10 mb = Cells(Rows.Count, "B").End(xlUp).Row
20 i = 2
30 For Each r In Range("B2:B" & mb)
40 If Range(r.Address) >= 5 Then
50 Cells(i, "D") = Range(r.Address).Offset(0, -1)
60 Cells(i, "E") = Range(r.Address)

70 i = i + 1
80 End If
90 Next

End Sub

Sub test2() 'Resize
Dim i As Long
Dim r As Range
Dim mb As Long

10 mb = Cells(Rows.Count, "B").End(xlUp).Row
20 i = 2
30 For Each r In Range("B2:B" & mb)
40 If r >= 5 Then
50 Range(r.Address).Offset(, -1).Resize(, 2).Copy Destination:=Cells(i, "D")

60 i = i + 1
70 End If
80 Next

End Sub

補足

Copy Destination:=Range("D1")の代わりに以下を使われていますが
情報をWEBで探してもヒットしないのですがどこかで情報が拾えますか？

.Copy [D1]

回答No.6

SI299792

最初何をしたいのか解らなかったので、違うプログラムを上げてしまいました。できたのならよかったです。

＞Copy Destination:=Range("D1")の代わりに以下を使われていますが
それは、別質問になるので新しい質問を立て下さい。
と言いたいところですが、別の方が回答しています。関連質問なので問題ないのかもしれません。（ここの規約はよくわからん）

このやり方は、Excel5.0 Visual BaseiとAppleScript（新居雅行）に乗っていました。
ネットにはないと思います。
短いのと[]なので()と区別できるという利点があります。
例えば、検索プログラムを作ろうとして、
　　～.Find(Range("A1") ,～)
と書くと、どの括弧が対応するのか私の頭では理解できません。
　　～.Find([A1] ,～)
なら見分けられます。

変数が使えないのが欠点です。例えば
　　Addr = "D1"
　　Range(Addr) = ～
の様な場合はできません。（今回のプログラムでは、位置が変わるので使えません）

＞Destination:=Range("D1")が[D1]にどうしてなるか
Destination:= は省略可能です。
たから、
　　Range("D1")
　　Destination:=[D1]
という書き方でもできます。

ついでですが、Evaluateは文字列で書かれた数式を計算する時に使います。例えば
A1に「3*12」が文字列で入っている場合
　　[B1] = Evaluate([A1])
でB1に36が入ります。

お礼

SI299792さん、解答ありがとうございます。

判り易い説明で理解が深まりました。

Range("A1")が[A1]と短く出来るのはカッコの違いを含めて
便利に使えそうです。

回答No.5

kkkkkm

> Destination:=Range("D1")が[D1]にどうしてなるかは判りませんでした。

Evaluate("D1")はRange("D1")とある意味同じで、
Destination:=は省略しても大丈夫(オプションが複数ある場合は順番さえあっていれば)なので
Destination:=Range("D1")を省略しまくると[D1]だけになります。

> Evaluate自体は便利そうですがどうも分かりにくく使う機会が無さそうです。

そうですね。まれにセルに記載した式「1+2*3」などを計算したいとかありますがその時には使えるかもです。
ただ、VBAじゃなくこっちを使う事しかありませんでした。
http://officetanaka.net/excel/function/tips/tips57.htm

また

WorksheetFunction.Sum(Range("A1:A5"))
を
Evaluate("SUM(A1:A5)")
↓
[SUM(A1:A5)]

にしたら「タイプ」は早いかもしれませんね。実行速度や他者に理解されるかどうかは別で。

間違っても
WorksheetFunction.Sum([A1:A5])
は無しで。

お礼

kkkkkmさん、解答ありがとうございます。

Destination:=が省略可能なので最終形が[D1]なのですね。

なるほどです。
コマンドが省略できるかを理解して
　省略されているかを見極めるのが必要になってきます。

型式が単純なのでCopy [D1]は覚えておいて損はないと思います。

教えてくれてありがとうございます。

回答No.4

kkkkkm

> Copy Destination:=Range("D1")の代わりに以下を使われています

Evaluateメソッドの省略形ですね。以下のサイトが参考になると思います。

Evaluateメソッド（文字列の数式を実行します）
https://excel-ubara.com/excelvba4/EXCEL_VBA_424.html

VBA Evaluate 関数：エクセル数式の呼び出し
https://www.tipsfound.com/vba/05003

どんなワークシート関数でも実行する
https://www.moug.net/tech/exvba/0100045.html

お礼

kkkkkmさん、解答ありがとうございます。

[D1]が、Evaluate("D1")の省略形と言う事は判りましたが、
Destination:=Range("D1")が[D1]にどうしてなるかは判りませんでした。

素人には、Evaluate自体は便利そうですがどうも分かりにくく使う機会が無さそうです。

回答No.3

kkkkkm

Range(r.Address)
は冗長な気がします(rでいけると思います)

r.Offset(, -1).Resize(, 2).Copy Destination:=Cells(i, "D")

お礼

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

上手く処理できないのでこねくり回して出てきたのが最初のコードです。
シンプルにrだけで良かったのですね。

SI299792さんが、
Copy Destination:=Range("D1")の代わりに以下を使われていますが
情報をWEBで探してもヒットしないのですがどこかで情報が拾えますか？

.Copy [D1]

回答No.2

imogasi

Googleで「VBA Resize」で照会するなどして、
https://excel-ubara.com/excelvba4/EXCEL210.html
などを読めば、質問をしなくても、おおよそのことは判るのでは？
RangeにResizeは使わなくても、今までで、ここのVBAの回答ぐらいの経験では、使わなくても済んだ。
ーー
参考
上記WEB記事で＞OffsetとResizeは、これを使用しないと出来ない、というような事はありません。
常に別の方法が存在していますし、その方が分かり易い場合も多いです。
ーー
セル結合のある場合の注意が載ってますが、VBAのデータベース的使い方では、セル結合は、避けるべきでしょう。
ーー
Resizeは、配列では使わざるを得ないことがありますが。これも、初心者の場合は、初めは、要素数を多め（予想MAX数+α）に取っておいて問題を避ける手もあるのでは。
　初心者の場合は、あまり無駄を避ける、に拘り過ぎるのも良くないように思う。
ーー
プログラムの学習では、他の点も含めて、我流にならないように注意することが必要で、そのために身近に助言者が必要だと思います。そうは言っても、これがソフト会社にでも就職しないと、難しいと思いますが。それを使えば、出来るからと言って、珍奇な方法（手法）にならないようにするのも必要ではないかな。

補足

>おおよそのことは判るのでは？

ちゃんと提示された記事を読んでOFFSETとの違いを理解できていれば
マイナスを指定できないのは明らかと言われるなら返す言葉は有りませんが、
RowSizemo及びColumnsSizeにマイナスを指定しては行けないとは書いていません。

身近に助言者もいないのでWEB情報がスキルアップに必須で
初歩的な間違いも多いのもお許しください。