Excel (VBA)

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

 
(Windows 10全般 : Excel 2013)
ブックの参照を、変数に置き換えたい
投稿日時: 20/07/26 12:21:26
投稿者: OkabeS

以下index,matchの計算式をVBAの中で使用する場合に、
以下部分を変数に変えたいのですが、エラーが発生(型が一致しません)が発生してしまいます。
おそらくColumns("A:K")のあたりがいけないのかもしれませんが
どこが間違っているのか、分かりません。。
ご教授頂きたくお願いいたします。
 
 
■元のコード(実行可能)
Selection.Formula = "=iferror(INDEX([オリジナル.xlsm]シート1!A:K,MATCH("& range("C2:C10")& ",[オリジナル.xlsm]シート1!H:H,0),1),"""") "
 
■やりたいこと
 →[オリジナル.xlsm]シート1!を変数に置き換えたい
original=ActiveWorkbook.Worksheets("シート1!")
 
 
■エラー発生 型が一致しません
Selection.Formula = "=iferror(INDEX(" & original.Columns("A:K") & ",MATCH(" & range("C2:C10")& "," & original.Columns("H:H") & ",0),1),"""") "

回答
投稿日時: 20/07/26 12:49:43
投稿者: simple

まず、ワークシート上で正常に動作する計算式を示されたらどうですか?
 
途中段階だけ見せられても意図もわかりにくいのではないかと危惧します。

投稿日時: 20/07/26 14:29:57
投稿者: OkabeS

simple様
 
早速のお返事ありがとうございます。
分かりにくく失礼しました。
 
ワークシート上で正常に動作する計算式は以下となります。
=IFERROR(INDEX([オリジナル.xlsm]シート1!A:K,MATCH($H2,[オリジナル.xlsm]シート1!H:H,0),1),"")
 
※目的は、オリジナルブック→転記先ブックへ、index matchを使用して転記するものです。
オリジナル.xlsm上でマクロ実行しますが、計算式自体は、転記先ブックに反映させます。
 
 

回答
投稿日時: 20/07/26 16:44:42
投稿者: hatena
投稿者のウェブサイトに移動

引用:
■元のコード(実行可能)
Selection.Formula = "=iferror(INDEX([オリジナル.xlsm]シート1!A:K,MATCH("& range("C2:C10")& ",[オリジナル.xlsm]シート1!H:H,0),1),"""") "
  
■やりたいこと
 →[オリジナル.xlsm]シート1!を変数に置き換えたい

 
下記でどうですか。
 
Dim strWS AS String
strWS = "[オリジナル.xlsm]シート1!"

Selection.Formula = "=iferror(INDEX("& strWS & "!A:K,MATCH(" & range("C2:C10") & "," & strWS & "H:H,0),1),"""") "

回答
投稿日時: 20/07/26 17:59:51
投稿者: simple

うーむ。
こうかもしれません。
 

Sub test2()
    Dim strWS As String
    strWS = "[オリジナル.xlsm]シート1!"
    Selection.Formula = _
        "=IFERROR(INDEX(" & strWS & "A:K,MATCH($H2," & strWS & "H:H,0),1),"""")"
End Sub

投稿日時: 20/07/26 18:56:15
投稿者: OkabeS

Simple様
hatena様
 
お返事ありがとうございます。
ブック名は可変にしたいため、
以下 [オリジナル.xlsm] をActiveWorkbookに変更したいです。
度々申し訳有りませんが、変更方法をご教授いただけたら幸いです。
 
Dim strWS AS String
strWS = "[オリジナル.xlsm]シート1!"

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

ちょっと待って下さい。
回答したのですから、それについてのコメントをしてください。
 
そのときの前提に基づいて、
できたのですか?できなかったのですか?

投稿日時: 20/07/26 19:24:21
投稿者: OkabeS

Simple様
 
言葉が足りず申し訳有りません。
教えて頂いたコードで、実行出来ました。
ありがとうございます。
 
[オリジナル.xlsm]をActiveWorkbookにする方法について
教えていただけますか?

回答
投稿日時: 20/07/26 20:30:22
投稿者: hatena
投稿者のウェブサイトに移動

コードの意味が理解できればちょっとした応用なんですけどね。
 

Dim strWS AS String
strWS = "[" & ActiveWorkbook.Name & "]シート1!"

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

マクロのコードを置いたブック  が、 "オリジナル.xlsm" であり、
それを参照する式、つまり、
=IFERROR(INDEX([オリジナル.xlsm]シート1!A:K,MATCH($H2,[オリジナル.xlsm]シート1!H:H,0),1),"")
を、ActiveBookのActiveSheetの選択されたセル範囲に書き込む、
ということを行ったつもりです。
 
私の疑問点は以下の二つです。
(1)余りよく読んでいなかったのですが、最初の質問中にある
引用:
■元のコード(実行可能)
 Selection.Formula = "=iferror(INDEX([オリジナル.xlsm]シート1!A:K,MATCH("& range("C2:C10")& ",[オリジナル.xlsm]シート1!H:H,0),1),"""") "

は本当に実行可能ですか?エラーになりませんか?
というのは、右辺で文字列と Range("C2:C10")という配列を連結操作していますが、
それは出来ない相談ですよね。
 
(2)もう一つの質問(こちらが重要)は、
引用:
[オリジナル.xlsm]をActiveWorkbookにする方法について
教えていただけますか?
とのこと。
アクティブブックに書き込む式であれば、アクティブブックのブック名を付ける必要性は
ないんじゃないですか?
 
そうではなくて、マクロを書き込んでいるブック名ということであれば、
strWS = "[" & ThisWorkbook.Name &  "]シート1!"
といった文字列にすることは考えられます。

投稿日時: 20/07/26 21:13:10
投稿者: OkabeS

simple様
 
早速のご回答ありがとうございます。
教えていただいた以下やり方で解決しました。
strWS = "[" & ActiveWorkbook.Name & "]シート1!"
 
自力で作った段階では、"[]" は必要無いと勝手に解釈してしまい
エラーが発生していました。
 
色々とお手間をお掛けしました。
自分なりに簡略化してお伝えしようと部分が、理解不足で裏目に出てしまいました。
以下ご質問に対しご回答します。
 
(1)について
range("C2:C10")はご指摘の通り間違いです。
大元のコードはまた別の変数を使っており、長くなるため
置き換えた際に書き間違えてしまいました。
手間をかける要因になり反省しています。
 
■元のコード(実行可能)
 Selection.Formula = "=iferror(INDEX([オリジナル.xlsm]シート1!A:K,MATCH("& range("C2:C10")& ",[オリジナル.xlsm]シート1!H:H,0),1),"""") "
 
(2)について
ご指摘の後者、「マクロを書き込んでいるブック名」=アクティブブックです。
以下処理の流れを考えています。
「オリジナルブック(マクロ書き込み) → 転記 → 転記先ブック」
 
教えて頂いたThisWorkbook.Nameの方が応用が効くためこちらに書き換えます。
恥ずかしながら「activeworkbookを使う」と思いこんでいました。。
 
以上大変助かりました。ありがとうございました。