Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
型が一致しませんMSGがでます。
投稿日時: 20/07/25 21:39:40
投稿者: ぜんだまん
メールを送信

select等を使わないプロシージャの書き方を覚えようと思っていますが、
変数の型の部分で躓いています。
次の"Set rng = Range("E24").Resize(maxRow, maxCol)"でエラーがでて止まります。
私が実行したい動作として「E24を選択後、データが入っている最終行(E26)まで範囲を拡張、
右に1つ列を拡張する(E24:F26)」としたいと考えています。
 
Option Explicit
Private Const maxCol As Long = 2
Private maxRow As Long
 
Sub test() 'E24選択、データが入っている一番下まで選択、右に一列拡張
 
    Dim rng As Worksheet
    maxRow = Cells(Rows.count, 1).End(xlUp).Row
 
    Set rng = Worksheets("1")
    Set rng = rng.Resize(maxRow, maxCol)
        
End Sub

回答
投稿日時: 20/07/25 22:35:46
投稿者: simple

Dim rng As Worksheet                  '(1)
Set rng = Worksheets("1")             '(2)
Set rng = rng.Resize(maxRow, maxCol)  '(3)

(1)で、ワークシート変数 rngを宣言し、
(2)で、ワークシートへの参照を変数rng に代入しています。ここまではよい(*)です。
  
しかし、(3)ではそのワークシートのResizeメソッドを呼んでいるんですかね。
でも、ワークシートオブジェクトにそんなメソッドないですよ。
  
(*)それと、名前は適切なものにしたほうがいいですよ。
rngという名前のワークシート変数って、自分が混乱しませんか?

回答
投稿日時: 20/07/25 23:23:46
投稿者: WinArrow
投稿者のウェブサイトに移動

ぜんだまん さんの引用:

次の"Set rng = Range("E24").Resize(maxRow, maxCol)"でエラーがでて止まります。

という説明から考えうに
 
> Dim rng As Worksheet
は、
> Dim rng As Range

> Set rng = Worksheets("1")
は、
    Set rng = Range("E" & maxRow)
でしょうね・・・
但し、maxRowは、A列の最終行でよいのかな?
 
 
※Selectを使わない
・・・と言うことは、記述しないということ同じではありません。
そのあたりも勉強しましょうね・・・
 

回答
投稿日時: 20/07/25 23:42:51
投稿者: simple

最初の回答者のsimpleです。(最近は、回答者の区別を付けない人がいるので、あえて書きますww。)
 
いずれにしても、
・Worksheetオブジェクトと
・Rangeオブジェクトとを
きちんと腑分けするところから出発して下さい。
 
# あんまり内容の無いコメントだったかも。

投稿日時: 20/07/25 23:45:15
投稿者: ぜんだまん
メールを送信

simple さんの引用:
Dim rng As Worksheet                  '(1)
Set rng = Worksheets("1")             '(2)
Set rng = rng.Resize(maxRow, maxCol)  '(3)

(1)で、ワークシート変数 rngを宣言し、
(2)で、ワークシートへの参照を変数rng に代入しています。ここまではよい(*)です。
  
しかし、(3)ではそのワークシートのResizeメソッドを呼んでいるんですかね。
でも、ワークシートオブジェクトにそんなメソッドないですよ。
  
(*)それと、名前は適切なものにしたほうがいいですよ。
rngという名前のワークシート変数って、自分が混乱しませんか?

 
こちらのサイトの、これだけは押さえるセル操作(2)で
"Rangeオブジェクト.Resize(変更後の行数, 変更後の列数)"
というのがありrangeオブジェクトでないとresizeは扱えないのかなと判断しました。
 
そこで下記のように変更しましたが、行方向への拡張でE24:F49までが選択されます。
maxRowが機能していません。
selectionとかもあまり使わない方向で書きたいのですが、どう書けばいいのでしょう
 
Sub test() 'E24選択、データが入っている一番下まで選択、右に一列拡張
     
    Dim ws As Worksheet
    maxRow = Cells(Rows.count, 5).End(xlUp).Row
  Set ws = Worksheets("1")
 
  ws.Range("E24").Select
 
  Selection.Resize(maxRow, maxCol).Select
        
End Sub

投稿日時: 20/07/26 00:03:21
投稿者: ぜんだまん
メールを送信

WinArrow さんの引用:
ぜんだまん さんの引用:

次の"Set rng = Range("E24").Resize(maxRow, maxCol)"でエラーがでて止まります。

という説明から考えうに
 
> Dim rng As Worksheet
は、
> Dim rng As Range

> Set rng = Worksheets("1")
は、
    Set rng = Range("E" & maxRow)
でしょうね・・・
但し、maxRowは、A列の最終行でよいのかな?
 
 
※Selectを使わない
・・・と言うことは、記述しないということ同じではありません。
そのあたりも勉強しましょうね・・・
 

 
maxRowはmaxRow = Cells(Rows.count, 5or6).End(xlUp).Rowですね
 
   Dim ws As Worksheet
    maxRow = Cells(Rows.count, 5).End(xlUp).Row
  Set ws = Worksheets("1")
  
  ws.Range("E24").Select
  
  Selection.Resize(maxRow, maxCol).Select
         
End Sub
 
このように修正しましたが、行方向への拡張でE24:F49までが選択されます。
データが何もないのにF49で止まって列方向に拡張してしまいます。
なぜでしょう

回答
投稿日時: 20/07/26 07:03:30
投稿者: simple

E列の最終行が26行として、
 
(1)Range("E24").Resize(26,2)というのは、
  >Rangeオブジェクト.Resize(変更後の行数, 変更後の列数)
   だから、変更後の行数は26行になりますね。(E24:F49です)
    
(2)あなたがやりたいことは、
  >'E24選択、データが入っている一番下まで選択、右に一列拡張
   だとすると、E24:F26を選択することでしょう?
   行数は3行じゃないですか?
   二つのことが違うことはわかりますか?
 
(3)色々な書き方がありますが、
   せっかく作ったmaxRowと活かして、一番直感的なのは、

   Set rng = Range("E24","F" & maxRow)
  でしょうか。
 
   Set rng = Range("E24",Cells(maxRow, "F"))
  とも書けます。
 
   最初のResizeを活かすなら、
   Set rng = Range("E24",Cells(Rows.Count, "E").End(xlUp)).Resize(,2)

 
なお、話を簡単にするために、ワークシートの指定は省略しています。
まずはここまで。
 
ワークシートの指定をするにはどうしますか?
考えてみてください。結構、難しいところがあります。

回答
投稿日時: 20/07/26 07:30:24
投稿者: MMYS

ヘルプにはResizeは「指定された範囲のサイズを変更します」
と説明されいてます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.resize
 
つまり、ぜんだまん さんが書いたコードは下記と同じです。
 
Range("E24").Resize(26, 2).Select
 
 
そして、上記コードは
 
"E24"を起点
・縦26行
・横2列
の範囲を選択する
 
なぜ数字が、26、2 なのか。それは
ぜんだまん さんがご自身てコードに記述したからです。
 
 
 
プログラムのコードは、英単語です。
Resize は英語です。日本語に直訳すると
 
 Resize → サイズ変更
 
Resizeは現在の選択範囲のサイズを変更します。って命令です。
 
 

投稿日時: 20/07/26 08:40:03
投稿者: ぜんだまん
メールを送信

simple さんの引用:
E列の最終行が26行として、
 
(1)Range("E24").Resize(26,2)というのは、
  >Rangeオブジェクト.Resize(変更後の行数, 変更後の列数)
   だから、変更後の行数は26行になりますね。(E24:F49です)
    
(2)あなたがやりたいことは、
  >'E24選択、データが入っている一番下まで選択、右に一列拡張
   だとすると、E24:F26を選択することでしょう?
   行数は3行じゃないですか?
   二つのことが違うことはわかりますか?
 

 
simple様返信ありがとうございます。
 
ws.Range("E24").Select
Selection.Resize(maxRow, maxCol).Select
 
という記述ではE24を選択して、選択範囲をmaxRow、データが入っている一番下のE26まで下り、
maxColでは2列を宣言(Private Const maxCol As Long = 2)してるのでE24:F26が変更後の
範囲と考えていたのですが、Selection.Resize(maxRow, maxCol).Select←この書き方だと
Range("E24").Resize(26,2)←このように指示をだしていることになるのでしょうか?

投稿日時: 20/07/26 08:58:59
投稿者: ぜんだまん
メールを送信

MMYS さんの引用:
ヘルプにはResizeは「指定された範囲のサイズを変更します」
と説明されいてます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.resize
 
つまり、ぜんだまん さんが書いたコードは下記と同じです。
 
Range("E24").Resize(26, 2).Select
 
 
そして、上記コードは
 
"E24"を起点
・縦26行
・横2列
の範囲を選択する
 
 

Range("E24").Resize(26, 2).Select←このように記載して覚えはありません。
Range("E24").Resize(maxRow, maxCol)とは書きました。
maxRowはE列でデータが入っている最下行のE26までを選択、maxColは右にその位置から+1列追加ではないの?
上記が26行さがって、2列右に移動するという意味になるのでしょうか?
その辺の説明をおねがいできますか?

投稿日時: 20/07/26 09:29:54
投稿者: ぜんだまん
メールを送信

simpleさん WinArrowさん MMYSさん回答ありがとうございます。
下記のコードで一応動作は思うように動きました。
一番下の行のような書き方は自分の想像の外にありました。
精進します。
 
Sub test4() 'E24選択、データが入っている一番下まで選択、右に一列拡張 7/26
  
Private Const maxCol As Long = 2
Private maxRow As Long
    
    Dim ws As Worksheet
    maxRow = Cells(Rows.count, 6).End(xlUp).Row
  
Set ws = Worksheets("1")
ws.Range("E24", "F" & maxRow).Select
 
 
End Sub

回答
投稿日時: 20/07/26 09:33:10
投稿者: simple

引用:
Selection.Resize(maxRow, maxCol).Select←この書き方だと
Range("E24").Resize(26,2)←このように指示をだしていることになるのでしょうか?

そうです。
・SelectionがRange("E24")で、
・maxRowが26で
・maxColが2ですから
そうなりますね。
 
そして、その意味は、既に指摘があるように、
"E24"を起点に
・縦26行
・横2列
の範囲
という意味です。
これを認めないと前に進めません。

投稿日時: 20/07/26 09:43:24
投稿者: ぜんだまん
メールを送信

simple さんの引用:
引用:
Selection.Resize(maxRow, maxCol).Select←この書き方だと
Range("E24").Resize(26,2)←このように指示をだしていることになるのでしょうか?

そうです。
・SelectionがRange("E24")で、
・maxRowが26で
・maxColが2ですから
そうなりますね。
 

 
 simpleさん回答ありがとうございます。
 
maxrowは行の"数値"のみを取得してその数値を移動幅にするということを理解していませんでした。
maxrowは最下行がE26なのでその行までを選択範囲とすると理解してました。
1つ勉強になりました。
辛抱強く指導していただき、助かりました。
またよろしくお願いいたします。