質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

354閲覧

関数の自動転記マクロ

MKHALFMOON

総合スコア27

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2017/07/25 06:20

編集2017/07/31 00:53

###前提・実現したいこと

Excel VBAで他のシートのセルの内容を自動転記させるマクロです。
アプリケーション定義およびオブジェクト定義がどこでされるべきなのかも
分からない状態です。
関数自体は手打ちであれば問題なく動作します。

###発生している問題・エラーメッセージ

実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。

###該当のソースコード
Excel VBA
---以下、作成したコード---

Sub For_Next()
'変数の宣言
Dim i As Long '行数カウント
Dim s, sNum, sCell, sValue, temp As String

'loop For i = 7 To 49 sNum = CStr(i) '転記先のセル sCell = "D" + sNum + "" '参照元のセル s = "3週!$BH" + sNum + "" '代入する関数 sValue = "=IF(NOT((INDIRECT(""" + s + """))=""),INDIRECT(""" + s + """),"""")" '実際にセルに表示される関数見本 '=IF(NOT((INDIRECT("3週!$BH7"))=""),INDIRECT("3週!$BH7"),"") '転記先のセルを指定 Worksheets("4週").Activate Range(sCell).Select '転記処理をさせたい(ステップインがここで止まる) ActiveCell.Range(sCell) = sValue Next i 'loop end

End Sub

---作成したコード、終わり---

以上、宜しくお願い致します。

※2017/7/31追記
今回作成したかったVBAが完成致しました。
ご回答頂いた皆様ありがとうございました。

VBA

1Option Explicit 2 3Sub For_Next2() 4 '変数の宣言 5 Dim i As Long '行数カウント 6 Dim s As String 7 Dim sValue As String 8 Dim rCell As Range 9 Dim week As Integer 10 week = 1 11 12 'loop 13 For week = 1 To 4 14 '転記先のワークシートを指定 15 Dim shtOut As Worksheet 16 Set shtOut = Worksheets((week + 1) & "週") 17 18 For i = 5 To 49 19 '転記先のセル指定 20 Set rCell = shtOut.Cells(i, "D") 21 22 '参照元のセル 23 s = "" & week & "週!$BH" & CStr(i) & "" 24 25 '代入する関数 26 sValue = "=IF(" & s & "="""",""""," & s & ")" 27 '実際にセルに表示される関数見本 ※今回の内容であればINDIRECTを使う必要はないので、この式で十分だと思います。 28 '=IF(3週!$BH7="","",3週!$BH7) 29 30 '転記先のセルに関数式を反映 31 rCell.Formula = sValue 32 33 Next i 34 35 Next week 36 'loop end 37 38End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

指摘したい点はいくつかありますが、現時点でエラーの原因となっているのはRange(sCell).Selectの部分のようですね。
上記を行いたいのであれば、たとえばActiveSheet.Range(sCell).Selectのようにシートを指定してあげれば動作すると思います。

ただ、今回提示いただいたコードは
①出力シート("4週")を選択
②出力セルを選択
③アクティブシートのアクティブセルに対して処理をする
という流れになっているようですが、アクティブシートやセルを切り替えながら行う処理は処理が重くなる原因にもなり、また処理中に他のExcelファイルが開かれたりするとそちらがアクティブシートになるため誤動作したりもします。

このため、アクティブシートやセルは切り替えず、参照や書き換えだけ行う方法をお勧めします。

以下は上記を盛り込んだサンプルです。

Sub For_Next2() '変数の宣言 Dim i As Long '行数カウント Dim s As String Dim sValue As String Dim rCell As Range '転記先のワークシートを指定 Dim shtOut As Worksheet Set shtOut = Worksheets("4週") 'loop For i = 7 To 49 '転記先のセル指定 Set rCell = shtOut.Cells(i, "D") '参照元のセル s = "3週!$BH" & CStr(i) & "" '代入する関数 sValue = "=IF(" & s & "="""",""""," & s & ")" '実際にセルに表示される関数見本 ※今回の内容であればINDIRECTを使う必要はないので、この式で十分だと思います。 '=IF(3週!$BH7="","",3週!$BH7) '転記先のセルに関数式を反映 rCell.Formula = sValue Next i 'loop end End Sub

参考になれば幸いです。

投稿2017/07/26 01:25

jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

MKHALFMOON

2017/07/26 07:53

回答いただきありがとうございました。大変参考になりました。 追加の質問になってしまうのですが、 マクロ適用ワークシートが5枚(1週~5週)とあり、 Set shtOut = ~ を5週 s = "4週!$BH" & CStr(i) & "" とした場合、マクロ実行時、他のブックは開いていないのに 「実行時エラー'9':インデックスが有効範囲にありません。」 となってしまうのはなぜなのでしょうか。
jawa

2017/07/28 00:56

実際にエラーとなるコードを見ないと何とも言えませんが、おそらく記載を省略されている `Set shtOut = ~ を5週` の部分に問題がありそうな気がします。 存在するシート数以上に参照しようとしているとか。
MKHALFMOON

2017/07/28 06:23 編集

確かにご指摘頂いている行でステップインが中断されます。 回答していただいたコードを Set shtOut = Worksheets("4週") ↓ Set shtOut = Worksheets("5週") s = "3週!$BH" & CStr(i) & "" ↓ s = "4週!$BH" & CStr(i) & "" のように単純に週数を変えて転記先が5週目の時だけ起こります。 余談ですが、 1週目から2週目、2週目から3週目、…、4週目から5週目 と変数で週数を変えるForNextの中に回答のコードをネストさせてコンパイルエラーはなかったのですが、 その場合も5週目に入ったところで 値の参照:5週 といったタイトルのウィンドウが立ち上がってしまいますので必要以上の 参照処理が行われているとは何となく思います。 セルの結合数や書式などが原因ではないかと転記元と先で同じになるよう 見直しはしましたが、他で引っかかっているのでしょうか。
jawa

2017/07/28 06:37

"5週"という名前のシートが存在しないのではないかと思います。 全角半角の違い("5週"はNG)などはないでしょうか?
MKHALFMOON

2017/07/31 00:47

「5週」のシートにつきましてはシート名が「5週 」のように半角ブランクが混入していたのが原因でした。初歩的ミスでした…
guest

0

"が足りてないようです

sValue = "=IF(NOT((INDIRECT(""" + s + """))=""),INDIRECT(""" + s + """),"""")"

sValue = "=IF(NOT((INDIRECT(""" + s + """))=""""),INDIRECT(""" + s + """),"""")"

投稿2017/07/25 06:42

takito

総合スコア3111

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問