[[20210919140511]] 『条件に一致した列の貼り付け』(なつ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『条件に一致した列の貼り付け』(なつ)

初めまして。
VBAで題記件、エラーになってしまい教えて下さい。

<やりたい事>

条件.xlsxのsheet1のA2に入力してある月と一致する月を転記先.xlsxの1行目から探して(下記例で言うと、C1セルが該当)、一致する列を変数に格納。
その月に、左隣りの列と同じ数式をコピーしてきたい。(相対参照で)

転記先.xlsxのsheet1

    A    B    C    D   
 1 No. 8月   9月  10月
 2 1   数式
 3 2   数式
 4 3   数式   
 5 4   数式
 6 5   数式

条件.xlsxのsheet1

 A2セルに「9月」と入力してある

<考えたコード>
Dim myRange As Range
Dim search As Range
Dim tsuki As String

Set myRange = Workbooks("転記先.xlsx").Sheets("sheet1").Range("B1:D1")

Set search = myRange.find(Workbooks("条件.xlsx").Sheets("sheet1").Range("A2").Value, lookat:=xlWhole)

tsuki = search.Column

Workbooks("転記先.xlsx").Sheets("sheet1").Columns(tsuki - 1).Copy
Workbooks("転記先.xlsx").Sheets("sheet1").Columns(tsuki).PasteSpecial Paste:=xlPasteFormulas 

ですが、下記の部分で「実行時エラー"1004" アプリケーション定義エラーまたはオブジェクト定義エラー」となってしまいます。

↓↓
Workbooks("転記先.xlsx").Sheets("sheet1").Columns(tsuki).PasteSpecial Paste:=xlPasteFormulas 

変数:tsukiには、3と正しく格納されているのですが、なぜか貼り付けられません。
列ごと一気に選択してコピーペーストしようとしているからダメなのでしょうか。できればB2から最終行(B6)までの数式をコピーして、C2から最終行まで貼り付ける…という式にしたかったのですが、それもエラーになってしまい、
列いっぺんにコピーしたらコピーまでは出来た感じです。
このあたりも教えて下さるとうれしいです。

宜しくお願いいたします。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


 >Dim tsuki As String
 >変数:tsukiには、3と正しく格納されているのですが、

 変数:tsukiは文字型ですよね。

 でもsearch.Column は数値です。

(半平太) 2021/09/19(日) 16:01


半平太様

ありがとうございました。
型をLongにして、範囲指定もLastRowの変数を加え、下記に変えたら一応できました。これで合っていますでしょうか。
Longとintegerどちらでもこの場合は大丈夫でしょうか。
色々試して何度もやっていたら、エラーになる時があり、あれ?と思ったら今はまた出来るようになったのですが…。謎です。

Dim myRange As Range
Dim search As Range
Dim tsuki As Long
Dim LastRow As Long

Set myRange = Workbooks("転記先.xlsx").Sheets("sheet1").Range("B1:D1")
Set search = myRange.find(Workbooks("条件.xlsx").Sheets("sheet1").Range("A2").Value, lookat:=xlWhole)
tsuki = search.Column

LastRow = Workbooks("転記先.xlsx").Sheets("sheet1").Range("A2").End(xlDown).Row

Workbooks("転記先.xlsx").Sheets("sheet1").Range(Cells(2, tsuki - 1), Cells(LastRow, tsuki - 1)).Copy
Workbooks("転記先.xlsx").Sheets("sheet1").Cells(2, tsuki).PasteSpecial Paste:=xlPasteFormulas

お手数をお掛けして申し訳ありませんが、今一度、お願いいたします。
 
(なつ) 2021/09/19(日) 17:08


 横から参加ですみません。

 >Longとintegerどちらでもこの場合は大丈夫でしょうか。
 問題ありませんが、最近は整数を扱う場合は、Long型が主流のようです。
 一例として下記サイトをご参照ください。
https://moripro.net/vba-integer-long/

 コードの方ですが、他人様のコードに口出し出来るほどVBAを理解しておらず恐縮ですが、
 ご提示のコードでRangeオブジェクトを変数に格納しているのと同様に、
 WorkbookやWorksheetなどのオブジェクトも変数に格納できます。
 今回、ほとんどの処理が Workbooks("転記先.xlsx").Sheets("sheet1") で行われているので、
 変数 ws としてまとめてみました。

 Dim myRange As Range
 Dim search As Range
 Dim tsuki As Long
 Dim LastRow As Long

 Dim ws As Worksheet
 Set ws = Workbooks("転記先.xlsx").Sheets("sheet1")

 Set myRange = ws.Range("B1:D1")
 Set search = myRange.Find(Workbooks("条件.xlsx").Sheets("sheet1").Range("A2").Value, lookat:=xlWhole)
 tsuki = search.Column
 LastRow = ws.Range("A2").End(xlDown).Row
 With Range(ws.Cells(2, tsuki), ws.Cells(LastRow, tsuki))
     .Offset(, -1).Copy
     .PasteSpecial Paste:=xlPasteFormulas
 End With

 最後の部分の様に、同じオブジェクトに対して複数処理を行うときは、
 Withステートメントで括ったりもできます。
 .Offset(, -1)で範囲を左に1つずらしてからコピーしています。
 .Resizeと合わせてWeb検索して理解を深めると良いと思います。
(#) 2021/09/19(日) 18:24

♯様
Long型が主流との事、貴重な情報ありがとうございます。
また、コードのシンプルな書き方を教えて頂き、大変勉強になります。
ありがとうございました!!

ちなみに、今回私は整数を扱うのに文字列を使ってしまった事により動きませんでしたが、
例えば、今回の事例の月は2104, 2105…というものだったので整数型で動きましたが、
もし21年4月、21年5月…だった場合は、
半平太様からご指摘頂いた下記を両方満たすためにもtsukiの型はvariantにすれば良いのでしょうか?
〉変数:tsukiは文字型ですよね。

 〉でもsearch.Column は数値です。
どなたか教えて頂けますと幸いです。
(なつ) 2021/09/19(日) 19:44

 search.Column とはつまり、Findで見つけたセル範囲 search の Column(列番号)ということですよね。
 列番号すなわち整数の値です。それを格納する変数 tsuki は、整数を収めるLong型で良いと思います。
 「21年4月」でも「2104」でも同じです。それらは
 検索する値 = Workbooks("条件.xlsx").Sheets("sheet1").Range("A2").Value
 検索する範囲= Workbooks("転記先.xlsx").Sheets("sheet1").Range("B1:D1")
 に入力されている値であるので、tsuki = search.Column とは別の話になります。
(#) 2021/09/19(日) 20:00

♯様
ありがとうございます。
頂いたコメントと、変数を収める型の説明をいま一度よく勉強し直してみます。
色々ご丁寧にありがとうございました。
(なつ) 2021/09/19(日) 20:09

横からですが私も参加で。

■1
既に指摘がありますが、↓がまぁおかしいですね。

 Dim tsuki As String

一応こうなっています、

 tsuki = search.Column・・・・・・「search.Column」の結果【数値】が返ってきますが、「String」型で定義してるので、
                            【数字(文字列)】として格納されます

 .Columns(tsuki - 1)・・・・・・・・【文字列】-1 となりますが、文字列の中身が”数値に読み替えられるもの”だったために
                            【Excel君の忖度】により計算結果である数値になり処理が進みます

 .Columns(tsuki)・・・・・・・・・・・・・.Columns(【文字列】)となり、そのような名前の列は無いので、ここでエラーが発生
                             ("A"列とか"B"列はありますが、"1"列とか"2"列なんてないですよね?)

■2
些細なことかもしれませんが、Sub〜End Subまでが一つの「プロシージャ」と呼ばれるかたまりなので、提示されるなら最初から最後まで提示されるとお互いに誤解がなくてよいと思います。

■3
Findメソッドを使用されていますが、見つからなかった場合はどうしますか?
その対策をしておかないと、見つからなかった場合に「search.Column」がエラーになります。

また、引数の「Lookin」を指定していないので、場合によってはあるはずなのに見つからないといったことが生じる可能性があります。

 【Findメソッドが失敗するとき】
https://www.moug.net/tech/exvba/0150111.html

■4

 LastRow = Workbooks("転記先.xlsx").Sheets("sheet1").Range("A2").End(xlDown).Row

ダメとは言いませんが、↑だとA2セル以下にデータがない場合最大行が返ってきてしまいます。
この点について「End(xlDown)」ではなく「End(xlUp)」によるアプローチもよく使われると思いますので、興味があれば検討してみるとよいとおもいます。

■5
ということなどを踏まえると↓のように整理することができると思います。

    Sub 名もなきマクロ()
        Dim 検索値セル As Range
        Dim search As Range
        Dim LastRow As Long

        Stop 'ブレークポイント
        Set 検索値セル = Workbooks("条件.xlsx").Sheets("sheet1").Range("A2")

        With Workbooks("転記先.xlsx").Sheets("sheet1")
            Set search = .Range("B1:D1").Find(検索値セル.Value, lookat:=xlWhole) 'Lookinも指定することを推奨
            If Not search Is Nothing Then
                LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                If LastRow < 3 Then Exit Sub

                .Range(Cells(2, search.Column - 1), Cells(LastRow, search.Column - 1)).Copy
                .Cells(2, search.Column).PasteSpecial Paste:=xlPasteFormulas
            Else
                MsgBox "「" & 検索値セル.Value & "」は発見出来ませんでした"
            End If
        End With
    End Sub

興味があれば【ステップ実行】して研究してみてください。

※ステップ実行という言葉を聞いたことが無ければ↓をよんでみてください。

 【ステップ実行】
https://www.239-programing.com/excel-vba/basic/basic023.html
http://plus1excel.web.fc2.com/learning/l301/t405.html

また、以下も知っておいて損は無いと思います。

 【イミディエイトウィンドウ】
https://www.239-programing.com/excel-vba/basic/basic024.html
https://excel-ubara.com/excelvba1/EXCELVBA486.html

 【ローカルウィンドウ】
https://excel-ubara.com/excelvba4/EXCEL266.html
http://excelvba.pc-users.net/fol8/8_2.html

 【ブレークポイント】
https://www.239-programing.com/excel-vba/basic/basic022.html
https://www.tipsfound.com/vba/01010

(もこな2 ) 2021/09/20(月) 11:33


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.