[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件に一致した列の貼り付け』(なつ)
初めまして。
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
ちなみに、今回私は整数を扱うのに文字列を使ってしまった事により動きませんでしたが、
例えば、今回の事例の月は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
■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.