Excel 入力して置換するマクロ

質問

Excel　入力して置換するマクロ

同一書式で作成された表がある複数の書式シートのデータを、同一ブック内にあるデータ集約するための集約シートが「表 あ」にあります。
都合上、集約シートには表は、「表 あ」しかなく、１つの書式シートのデータのみしか表示していません。

書式シートのデータを参照する計算式内のシート名にあたる『Sheet1!』の部分を置換で他の書式シートに変えたいのですが、対象シートが複数あり上、誰でも置換をスムーズに行いたいので、インプットボックスにシート名を記載すれば置換できるマクロが作れないのか試行錯誤しております。

良い方法があればお教え下さい。

回答No.6

mu2011 · ベストアンサー

NO4です。
連続していない範囲は、Unionで1文にします。
Union(Columns("C"), Columns("E")).Select

お礼

ありがとうございます。

「Union」を使うんですね。
この質問をしてからたくさんのことを学びました。
本当にありがとうございます。

ちなみに、やっとデータが完成しました。
いつか質問しなくても自力で望むマクロが作成できるように、これから精進いたします。
また、何かありましたらよろしくお願いいたします。

回答No.5

Wendy02

>＞できれば、入力キャンセル時はExitした方が良いと思う。
>
>？？どうすれば「Exit」とできますか？

InputBox 関数で行うとしたら、InputBox 関数自体が過去のものですから、その判定も、レガシー(過去）関数--StrPtrでないと判別できません。現在のVBAでは、InputBox メソッドを使います。

また、入門者の人には強制しませんが、基本的に、対話型は、必ず、離脱方法のコードを書きます。また、想像できるエラーつぶしておくのも基本です。例：InputBox(関数）, Application.InputBox (メソッド）

>　Selection.Replace What:="*!", Replacement:="入力したシート名", LookAt:=xlPart, _
「'(アポストロフィ）」で、置換対象を明確に範囲づけるというのはアイデアで分かりますが、もともと考えすぎで、もう少し単純に考えたほうがよいですね。そうでなければ、Sheet名を探し出す方法はありますが、かなり難しくなります。

以下は、Sheet名のチェックはしていますが、同じワークシート内だけのチェックです。

'//
Sub Test1()
Dim srchTxt As Variant
Dim repTxt As Variant
Dim dum As Variant
Dim rng As Range
　 On Error Resume Next
　　Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
　 If rng Is Nothing Then MsgBox "対象セルが見つかりません。", vbExclamation: Exit Sub
　　srchTxt = Application.InputBox("検索値を入力してください" & vbCrLf _
　　　& "例：" & Sheets(1).Name, "検索値", Type:=2)
　　If VarType(srchTxt) = vbBoolean Or Trim(srchTxt) = "" Then Exit Sub
　　'シート名のチェック
　　dum = Worksheets(srchTxt).Range("A1").Value
　　If Err.Number > 0 Then MsgBox srchTxt & vbCrLf & "シート名がエラーです。", 16: Exit Sub

　　repTxt = Application.InputBox("置換値を入力してください" & vbCrLf _
　　　& "例：" & Sheets(2).Name, "置換値", Type:=2)
　　If VarType(repTxt) = vbBoolean Or Trim(repTxt) = "" Then Exit Sub
　　'シート名のチェック
　　dum = Worksheets(repTxt).Range("A1").Value
　　If Err.Number > 0 Then MsgBox repTxt & vbCrLf & "シート名がエラーです。", 16: Exit Sub
　On Error GoTo 0
　　 rng.Replace What:=srchTxt, Replacement:=repTxt, LookAt:=xlPart
End Sub

お礼

ご回答ありがとうございます。

本当に入門者も入門者なので、みなさまにご迷惑おかけしております。
いろいろご指摘頂きありがとうございます。

質問する前にいろいろ調べたおり、Wendy02 様にお教え頂いたようなマクロを（雰囲気が似てるの意味）発見したのですが、まったく理解不能で何かあった時に自己修正ができないため、スルーしていました (>_<;)

マクロになれるためにも、お教え頂いたマクロを頑張って解読して勉強してみます。

本当にありがとうございます。

回答No.4

mu2011

NO3です。
innputboxコードの直後に以下のコードをお試しください。
　If 入力した値 = False Then Exit Sub

キャンセルなのでエラーを出す必要はないと思いますがどうしてもエラーメッセージを表示したいのであればMsgboxコードで表示して下さい。

お礼

ありがとうございます。

作業をしながら少しづつですが理解しております。（本当に少しづつです）

Columns("C:C").Select

上記で指定している「C列」を、「C列」と「E列」など隣接しない2列を指定することは可能ですか？
いろいろやってみているのですが…もう少し頑張ってみます。

本当にありがとうございます。

回答No.3

mu2011

NO1です。
NO1です。
前回、置換の共通文字列をsheetと！で間の文字をワイルドカードとしてSheet*!で提案しました。
回答補足から実際のシート上のシート名は引用符で確定できるのであれば、以下のようなコードで如何でしょうか。
因みInputboxの第3引数にディフォルトシート名（存在するシート名称）を指定しておけば、入力の補足になりますが如何でしょうか。
できれば、入力キャンセル時はExitした方が良いと思う。

Sub　Sample()
Dim 入力した数値 As Variant
入力した数値 = InputBox("シート名を入力して下さい", , "xxxxx")
Columns("C:C").Select
Selection.Replace What:="'*'", Replacement:="'" & 入力した数値 & "'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

お礼

何度もありがとうございます。

出来ました！！本当にありがとうございます。

＞因みInputboxの第3引数にディフォルトシート名（存在するシート名称）を指定しておけば、入力の補足になりますが如何でしょうか。

本当ですね。(>_<;)/
不特定の方が利用するので、採用させていただきました。ありがとうございます。

＞できれば、入力キャンセル時はExitした方が良いと思う。

？？どうすれば「Exit」とできますか？

また、ずうずうしく追記させていただきますが、何も入力せずに実行したときに、シート名なしで置換されないでエラーを出すようなことができますか？

回答No.2

imogasi

一般化しようとして、質問文の表現が抽象化していて、細部が良くわからない。回答を依頼するなら
実例でも挙げて回答依頼すべきと思う。
ーーー
書式シートと書式をつけるのはなぜ。シートで良いのでしょう。エクセルの「書式」と、変な誤解をする。
ーー
言い換えると
ブックＡにＳｈｅｅｔ１、Ｓｈｅｅｔ２・・が在る。
ブックＡには集約シートＳｈｅｅｔ11(11は仮の仮定）にＳｈｅｅｔ１のデータを集落したデータが在る。
ここには関数式が入っている、のかな。いま既にＳｈｅｅｔ１とかの限定をした式が入っているのか。
ブックＡのＳｈｅｅｔ１１の集計表Ｘを別セル範囲に、それぞれ元データシート数だけ（集計表Ｙ、Ｚ・・）コピーして、これらがＳｈｅｅｔ２・・の集計をおのおの行うように式をかえたいということか。
まず集計表Ｙのセル範囲で、Ｓｈｅｅｔ１！で限定している部分をＳｈｅｅｔ２！に置き換えてみて、
うまく行くかやってみてはどうでしょう。質問はそれからだ。
ちなみに、数式の部分も置換は可能。置換対象で数式を指定。
ーーー
私が誤解している点があれば、後の回答者のために、指摘しておいてください。
ーー
ＶＢＡでやるなら、このていどのことならマクロの記録の修正で出来る。上記の操作をマクロの記録を取り、Ｓｈｅｅｔ３以下に使えるよう修正すれば仕舞いでしょう。

お礼

ご回答ありがとうございます。

すみません。
詳細画像を添付したのですが、画像添付のマークは出るものの表示ナシの状態でして…おさがわせいたしました。補足にて再度説明させていただきます。

補足

＊「集約シートＳｈｅｅｔ11」 の置換対象とたる列には下記の数式が入力されております。
　 =IF(ISERROR(INDIRECT("'シート名'!D2")),"",INDIRECT("'シート名'!D2"))

＊「データシート」にはそれぞれ数字や数式、ではなく文字が入力され、参照元は１列のみです。

やりたいことは、「集約シートＳｈｅｅｔ11」 において、マクロなので置換対象とたる列に記載された数式内のシート名を示す箇所を、インプットボックスに変更したい「データシート」名をいれることにより一発置換がしたいのです。

マクロを使い始めて数日の初心者のためご迷惑をおかけしておりますが、よろしくお願いいたします。

回答No.1

mu2011

一例です。
置換の検索文字列を「Sheet*!」、インプットボックスの入力文字列を置換文字列にする方法は如何でしょうか。

お礼

早々のご回答、ありがとうございます。

Dim 入力したシート名 As Variant
　　　　入力した数値 = InputBox("シート名を入力して下さい")
　　Columns("C:C").Select
　　Selection.Replace What:="*!", Replacement:="入力したシート名", LookAt:=xlPart, _
　　　SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
　　　ReplaceFormat:=False
End Sub

※C列に入っている数式
　=IF(ISERROR(INDIRECT("'シート名'!D2")),"",INDIRECT("'シート名'!D2"))

上記内容で作成したのですが…失敗でした。
「"*!"」と、「"入力したシート名"」の箇所をいろいろ変えてみたものの惨敗でした。

（結果は変化なしか、↓こんな感じでした）
■入力したシート名!入力したシート名!$D2"))
■=IF(ISERROR(INDIRECT("入力したシート名D2")),"",INDIRECT("入力したシート名D2"))

再度、お助けいただけますでしょうか？