EXCEL2002で、VBEの記述のセル参照について

質問

EXCEL2002で、VBEの記述のセル参照について

Sub データ置換(1)()
With Range("C4:E4")
.Replace What:="8038", Replacement:="A4"
End With

End Sub

上記コードの３行目、右端、
Replacement:="A4"
の"A4"を、セル参照にして、"9984"等、可変する4桁の数字を入力して、
"8038"⇒⇒⇒⇒⇒⇒⇒⇒⇒⇒"9984"
というように、いつ何時でも、変更可能な『好きな数字』を、マクロボタン一つで変えられるように、編集したいのですが、どのように修正したら良いでしょうか？

　引数に関する初歩的な質問ですが、何分、初心者でありまして、優しくお答えお願いします。

回答No.10

hana-hana3 · ベストアンサー

＞たとえ、セル内が数式であったとしても、容易に探し出してきていました。

やっと理由が解りました。
「置換」というのは、通常は文字列に対して行う物です。
置換対象が「数式」であるという条件を提示されていなかったので、ＶＢＡの動作では「文字」を対象に動作するようにしました。
動作条件（置換対象）が提示されていなかったので、「一般的な回答」として文字列の置換動作を行うプログラムを書いたので、ご指摘のようなエラーが起きる事すら想定していませんでした。

Range("A1").Value　　＞　セルの「値」を取り出します。
Range("A1").Formula　＞　セルの「数式」を取り出します。

通常の検索では自動的に一致する物をチェックしてくれますが、For Each でオブジェクトを使うと、セルから取り出したい物を明示しないとエラーになるので、プロパティからきちんと指定する必要があります。

従って、RepStr = Num4ck(r.Value) を RepStr = Num4ck(r.Formula) に変更することで対処可能です。

今後、別件でも質問される事もあるかと思いますが、使用条件（あるいは求める仕様）などを極力明示して、参考データなども書き添えて頂ければ双方の無用な時間を費やす事を減らす事が出来ると思いますよ。

（どんなプログラマーでも、予測を超える事態や仕様に明示されていない事まで対処できませんから・・・m(__)m）

お礼

私がＶＢＡの専門用語を理解しておらず、勝手な推測と、当て推量により、たいへんな御迷惑を掛けてしまいました。

　ご指摘のように、質問の根幹に関わる、基本的な要件にさえ気づかず、見逃してしまい、自らの不明を恥じ入る次第です。

　ＶＢＡに関しては、ご指摘のように、
RepStr = Num4ck(r.Value) を RepStr = Num4ck(r.Formula) に変更することで対処可能となりました。

　これまで、長い間、私の拙い質問に、辛抱強く応じてくださり、本当に有難う御座いました。

　今後は、御指摘頂いた点を、まず、質問する前に、充分考慮したうえ、『自分の質問には、５Ｗ１Ｈ，環境条件、使用条件、求める仕様、等、が足りているかどうか？』よく考え、明示するよう勤めて参ります。

　今後も、私のような初心者に、御優しい、hana-hana3さんであることを、御期待します。今後とも宜しくお願いします。

補足

ポイントについては、★良回答以外にも、次点にも、ポイントを差し上げたい★、と、ボタンを押したのですが、『同一人物へのポイント付与』は不可能ということで在りました。
　良回答を下さった相談者の方には、ボランティアさんであるのですから、★”良回答であれば、同一人物であれ、何度でもポイントを差し上げたい”★のが常であり、それが出来ず、申し訳ありません。

回答No.9

hana-hana3

＞ひたすらイジッテいましたが、解かりませんでした。

数字に全角文字が使われていると言うことはありませんよね？
こちらとしても、セルの内容（状態）が解らないので何とも言えませんが、前回のエラー値がある事からして・・・セルの内容に式が入っているのでしょうか？

式の場合には、セルの内容と表示が異なるので置換することはできません。
（値に直せば可能です）

お礼

御回答有難う御座います。
　ご指摘の通り、初めから、セルの内容について、記しておくべきでした。申し訳ありません。

　★まず、このワークシートなのですが、所謂、『外部参照』という形で、アドインを使用した関数が、セル内に入っているため、

　式を書き記しますと、

　=RSS|'8038.T'!前日比率

　というようになっています。こうした式が入ったセルが、整然と並んでいることになります。

　★しかし、くれぐれも、申し上げたいのは、通常ですと、ツールバーの中の『編集』⇒『置換』をクリックしさえすれば、たとえ、セル内が数式であったとしても、容易に探し出してきていました。
　あくまで、御説明から、推察させていただきますと、セル単独では、キチンと識別できても、セルの集合体になってしまうと、識別・認識が困難となってしまうのでしょうか？

　そういった理解で宜しいでしょうか？

回答No.8

hana-hana3

セルのどこかがエラー[#N/A]になっていませんか？

http://www.moug.net/tech/exvba/0100037.htm

お礼

いち早い回答有難う御座います。
　ご指導通り、操作したところ、お陰様を持ちまして、
　仰るとおり、この問題に関するエラーは確かに出なくなりました。
　有難う御座いました。

　しかし、依然として続く問題、

　Ａ４⇒⇒⇒⇒⇒Ｃ４、Ｄ４、Ｅ４
　Ａ５⇒⇒⇒⇒⇒Ｃ５、Ｄ５、Ｅ５
　Ａ６⇒⇒⇒⇒⇒Ｃ６、Ｄ６、Ｅ６

といった、各行の先頭に対応した、４桁の数字（半角『1000～9999』）が、以降のセルで、★一度に全て『置換』することは不可能であり、一回のマクロボタンの実行で、上記３行分のうち、Ａ４の行、単独行のみしか変更されません。

　やはり、一行ずつしか変更出来ないのでしょうか？

　或いは、数字指定のウィンドウをわざわざ表示させる方法にもう一度改め、上記の『４桁の数字（半角『1000～9999』）』を範囲指定させて、その度、実行した方が、『急がば廻れ！』ではありませんが『遠回りなようでいて、安全で確実でしょうか？』

　この時間まで、ひたすらイジッテいましたが、解かりませんでした。
　どうぞ、ご教示、宜しくお願いします。

回答No.7

hana-hana3

＞　★一体、どのように対処したら良いでしょうか？

遅くなりました。
幾つか考えて見ましたが、理由が解りかねる状態です。

黄色く反転している状態で、
RepStr や r.Value の文字（変数）の部分にカーソルを置くと、内部の値が表示されるので、そこに表示された文字を教えてください。

お礼

御回答有難う御座います。
　いち早く、気づかず、申し訳在りません。
　黄色表示に、カーソルを当てると、次のように表示されました。

　RepStr=""

　r.Value =エラー2042

　宜しくお願い致します。

回答No.6

hana-hana3

＞左横に【⇒】が出て、”エラー”を指し示しています

きちんと説明していなくて申し訳ありませんでした。
先の回答全てが１セットなので、この２つのプロシージャがモジュールに書かれていないと動作しません。

一つのプロシージャにしてしまう事も可能でしたが、処理の見通しを良くするために、４桁の数字を探す作業を関数にして使用しました。

エラーが起きた部分で、Function Num4ck(STR As String)と言う関数にセルのデータを渡して、見つかった４桁の数値を受け取っています。
そのため、この関数がモジュールに書かれていないとエラーになってしまいます。

お礼

いち早い回答有難う御座います。
紛らわしい表現を使用して、申し訳ありません。

　＞２つあるうちの、上半分、１パターン目の方で

と、先に書いてしまったのですが、その後、★お書き頂いた全て★を書き込んでみましたところ、

　★実行時エラー’１３’
　★型が一致しません

と、エラーウィンドウが出て、『デバッグ』ボタンを押すと、
やはり、前回と同じ
一番上の文頭から、５行目

RepStr = Num4ck(r.Value)

の箇所が、”黄色の網掛け”になり、
左横に【⇒】が出て、”エラー”を指し示しています。

　★一体、どのように対処したら良いでしょうか？

”エラー”の状況については、『あるがままの状況』を、最も詳しく書いたつもりなのですが、他にも『知らなくてはならないこと』が御座いましたら、何なりと、お尋ね下さい！

回答No.5

hana-hana3

＞、"４桁の数字（なら全て）"を、その対象『検索値』として、含める

不可能では無いので、とりあえず作って見ました。
最初に見つかった４桁の数字（１０００以上の数値）を置き換えます。

Sub データ置換5()
Dim RepStr As String
Dim r As Range

For Each r In Range("C4:E104")
RepStr = Num4ck(r.Value)
If Cells(r.Row, 1) <> "" And RepStr <> "" Then
r.Replace What:=RepStr, Replacement:=Cells(r.Row, 1)
End If
Next
End Sub

Function Num4ck(STR As String)

Dim tmp As String
Dim i As Integer
Dim FLG As Boolean

For i = 1 To Len(STR) - 3
tmp = Mid(STR, i, 4)
If Val(tmp) >= 1000 Then
FLG = True
Exit For
End If
Next

If FLG Then
Num4ck = tmp
Else
Num4ck = ""
End If

End Function

お礼

何度も、同じような質問ばかりで、お手を煩わせたいへん申し訳ありませんでした。

　★hana-hana3様のお陰で、貴重な『実践』に役立つ知識を、得ることが出来ました。
　私のようなEXCEL VBAの使い方をする者にとって、非常に『得がたい経験』となりました。
　今後、益々、EXCEL VBAを使う事が『好き』になりそうな予感がします。これも、ひとえにhana-hana3様のお陰です。

　これからも、どうぞ、初心者の相談に『優しいhana-hana3様』でいて下さることを、心から祈ります。

　本当に有難うございました。

補足

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

　本当に申し訳ありません。
何度も、『これで終わりです。』と言いながら、再び、うまくいかない点が出てきてしまいました。
　その、うまくいかない点、なのですが、
　★御作り頂いた『ＶＢＡの記述』で、初めて”エラー”が出てしまいました。

２つあるうちの、上半分、１パターン目の方で
５行目

RepStr = Num4ck(r.Value)

の箇所が、”黄色の網掛け”になり、
左横に【⇒】が出て、”エラー”を指し示しています。

　一体、どのように対処したら良いでしょうか？

”エラー”の状況については、『あるがままの状況』を、最も詳しく書いたつもりなのですが、他にも『知らなくてはならないこと』が御座いましたら、何なりと、お尋ね下さい！

回答No.4

hana-hana3

お答えありがとうございます。
２つの手法を書いておきますので、参考にして下さい。

Sub データ置換4a()
Dim RepStr As String
Dim r As Range

RepStr = InputBox("検索値", "データ置換", "8038")
For Each r In Range("C4:E104")
If Cells(r.Row, 1) <> "" Then
r.Replace What:=RepStr, Replacement:=Cells(r.Row, 1)
End If
Next
End Sub

Sub データ置換4b()
Dim RepStr As String
Dim Str As String
Dim i As Integer

RepStr = InputBox("検索値", "データ置換", "8038")
For i = 4 To 101
Str = Cells(i, "A")
If Str <> "" Then
Range(Cells(i, "C"), Cells(i, "E")).Replace What:=RepStr, Replacement:=Str
End If
Next
End Sub

お礼

こちらこそ、有難うございます。
　『お答えありがとうございます。』なんて、とんでもない。
　もったいないお言葉です。

　私の質問の仕方・順番の悪さから来る手際の悪さで、お手を煩わせてしまったのですから、たいへん申し訳なく思っております。混乱させてしまい、すみません。

　今回、お教え頂いたコードを使い、他の関数の使用についても、幅広く応用を利かせていけることが、今後は出来るように思います。

　本当に有難うございます。

補足

追伸

　何度も、御質問申し上げてすみません。
そう容易く出来ることではない、と重々、承知してはおりますが、再び質問させて下さい。
　＞"8038"で固定
　ということで、一旦、申し上げてしまったのですが、翻って考えてみますと、
　"8038"⇒⇒⇒⇒⇒"４桁の数字（なら全て）"
という、より”広い、ゆるい括りの数字”で、
例として、"9984","1724","7898"というように、"４桁の数字（なら全て）"を、その対象『検索値』として、含める
　このように、幅を持たせた”数値範囲の指定”を
★コードの記述の操作で、変更できないでしょうか？★

　本当に、都合のいい話ばかりで申し訳ありません。
★メッセージウィンドウのポップアップ時での指定方法は存知ておりますが、私の望む操作ができると、格段に、スピードアップが出来るものですから、何か良い方法は御座いますでしょうか？

　一度、終わった話であると、当然、思われたでしょうが、其処を、何卒、宜しくお願いします。

　『出来なければ、出来ない。』と、ハッキリ仰って下さって結構ですので。

回答No.3

hana-hana3

＞対象となる全セルにおいて、予め"8038"と固定して、

RepStr = InputBox("検索値", "データ置換", "8038")
とするか、
RepStr = "8038"
として下さい。

他の部分の回答を書く前に少し質問させて下さい。
１．"C4:E104"と言う範囲は固定なのでしょうか？
それとも常に変化する範囲なのでしょうか？
毎回範囲を手動設定した方がよいのでしょうか？
２．Ａ列に空欄になる可能性（置き換わる文字を入れない）はあるのですか？

補足

拙い初学者の質問ながら、毎回、丁寧にお答え頂き有難うございます。
　私の質問の仕方・順番の悪さから来る手際の悪さで混乱させてしまい、たいへん申し訳在りません。

　お答えを左右しかねない、重要な条件について、ですが、
＞１．"C4:E104"と言う範囲は固定なのでしょうか？
　それとも常に変化する範囲なのでしょうか？

　固定であり、Ａ４～Ａ１０４に、常に対応しています。

＞２．Ａ列に空欄になる可能性（置き換わる文字を入れない）はあるのですか？

　エラーとならぬよう、必ず、４桁の数字が入力されており、
★先ほど、御説明で不足していたのですが、Ａ４～Ａ１０４に入力されている、”同じ数字は一つとしてない”４桁の数字１０１個に対応させて、マクロボタン一つ押すことで、Ｃ４～Ｅ１０４の”8038”が一斉に
置換わる。
　こうした形をイメージして下さるようお願いします。

回答No.2

hana-hana3

＞下の方向に、範囲とされる”列”を伸ばしていき、

下記で如何でしょうか？
最初のダイアログボックスが表示された時点で、シフトキーを押しながらＥ列の下端をクリック知れば、新しい範囲を指定出来ます。

Sub データ置換2()
Dim RepStr As String
Dim C As Range
Set C = Application.InputBox("検索範囲を選択して下さい。", "範囲選択", "C4:E4", , , , , 8)

RepStr = InputBox("検索値", "データ置換")
With C
.Replace What:=RepStr, Replacement:=Range("A4")
End With
End Sub

最初から範囲を指定しておくなら、先の文の１行を書き換えるだけで対応できます。

With Range("C4:E4")　→　With Selection

＞延伸したセルに、当然、対応させる形で、(A104)まで、各々対応させるには

同じ行のＡ列の文字に置換すると言うことでしょうか？
その場合、先の文では対応できません。
たぶん下記でＯＫかも。

Sub データ置換3()
Dim RepStr As String
Dim C As Range
Dim r As Range
Set C = Application.InputBox("検索範囲を選択して下さい。", "範囲選択", "C4:E4", , , , , 8)

RepStr = InputBox("検索値", "データ置換")
For Each r In C
r.Replace What:=RepStr, Replacement:=Cells(r.Row, 1)
Next
End Sub

お礼

いち早く、お答え頂いたうえ、今回もまた、完璧な御内容、誠に有難く、感謝致します。

　たいへんお骨折り頂き、こちらの環境を、考慮して頂いたうえで、下さった回答であること、たいへん感謝しております。
　私のワークシートにおいて、デフォルトの数値に関し、『条件設定』をお伝えして、おかなかったものですから、いけなかったと反省しております。

　『ウィンドウ操作の省力化、ショートカット』という観点から、
（１）★『検索値』として、改めて入力し直す数値は、対象となる全セルにおいて、予め"8038"と固定して、ウィンドウ操作の省力化を図り、★セルへの入力操作はマクロボタンを押す前の、Ａ４～Ａ１０４というＡ列セルへの入力だけに留めたいのですが、

　どのように、コードを変更すれば、可能となるでしょうか？

　また、私の説明不足により、逆にご質問頂いた
＞同じ行のＡ列の文字に置換すると言うことでしょうか？

について、『対応関係』を図示いたしますと、
"A4"⇒⇒⇒"C4"⇒⇒⇒"E4"
"A5"⇒⇒⇒"C5"⇒⇒⇒"E5"
"A6"⇒⇒⇒"C6"⇒⇒⇒"E6"
"A104"⇒⇒⇒"C104"⇒⇒⇒"E104"
という対応となります。御考慮下さい。

　再びの、質問で、たいへん申し訳在りません。前回の質問では、『回答への補足』と、『回答への御礼』を、二つ書いてしまうと、一杯になり、記載する箇所が、一つも無くなってしまい、気づいてはいたのですが、極めて重要な、この条件をお伝えできませんでした。

　誠に、たいへん申し訳在りません。

回答No.1

hana-hana3

Sub データ置換()
Dim RepStr As String
RepStr = InputBox("検索値", "データ置換")
With Range("C4:E4")
.Replace What:=RepStr , Replacement:=Range("A4")
End With
End Sub

お礼

いち早い、回答、誠に有難うございます。

初めて、目にするnputBox("検索値", "データ置換")
の意味を、VBAの教科書を見て、現在、自分のワークシートに当てはめています。この後、もう一度、質問させてください！

補足

再び、お願いします。
　実際に、自分のワークシートに当てはめてみて、操作の意味がわかりました。マクロボタンを押下して、最初に出てくるウィンドウで、『検索値』の箇所に、元々最初から入れておいた初期値を、『検索値』として入力し、すると、Ａ４セルの数字が、各セルで置換わるのですね。

　これに関連した質問として、
　頂きました回答コードの４行目
　With Range("C4:E4")
　を下の方向に、範囲とされる”列”を伸ばしていき、
　("C104:E104")
まで延伸して、範囲指定したしたうえ、延伸したセルに、当然、対応させる形で、(A104)まで、各々対応させるには、　
　どう書き換えたら良いでしょうか？

　”範囲指定”の初歩的な質問ですが、宜しくお願いします