第20回目 凝った計算式の作り方3、最後の調整

○第20回目　凝った計算式の作り方3、最後の調整

　問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。

　これは次のような意味です。
　65行目のL列の計算式は、"=sum(L17:L$60)"となっています。
　これを118(65+60-7)行目にコピーしますと、"=sum(L$60:L70)"となってしまうのです。これは無意味な計算式です。ちなみに、B列では、"=sum(B60:B$60)"となって、有効です。

　無効な計算式のセルを力技でクリアすというのもありますが、能がないので違い方法を考えるべきでしょう。
　エクセルの処理でやろうとすると、置き換えになります。無効な計算式は中に61以上の数値を含みますので、この性質を使います。
　"*61*"をヌル(結果的にクリアされる)に変えるのです。これを62,63…と必要なだけ繰り換えすのです。

　マクロではどうなるでしょうか。
　色々な方法が考えられます。

　1.計算式中、61以上のあるセルをクリアする。
　2.物理的階段状にセルをクリアする。
　3.複数の置き換えが可能なマクロで、置き換える。
　
　3.でやってみましょうか。著者もまだ作っておらず、使い回しがききそうと、いう理由。


　置換えに関して、マクロの記録で操作マクロをとって見ますと、次のようになります。
　　
Sub Macro1()
' Macro1 Macro
Range("X15:X18").Select
Selection.Replace What:="*61*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


　これを少しアレンジして、置換え元と置換え後が変数で動くかどうかを確かめます。

Sub a18Macro1()
'
' Macro1 Macro
a1 = "*61*"
a2 = ""
Range("X15:X18").Select
Selection.Replace What:=a1, Replacement:=a2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

　大丈夫です。動きました。(このように少しずつ確認していきます)


　次に、もっと進めて。

　置換え元の文字列、置換え後の文字列を作る。
　
Dim a_zen(20)
Dim a_go(20)

For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*"　　'61から81を含むセルを指定するための文字列
a_go(i) = ""　　　　　　　　'置換え後はヌルにする。すなわちクリア。
Next i
　

　置換え処理をループさせる。

AAA = Selection.Address　　　'実行前に指定した範囲の座標がここに入る
For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i


【完成版】
　
　Sub a18Macro1()
'複数項目の置換え。置換え元数値は自動生み出しとしている　
'置き換えたい範囲を指定したあとで実行する。

Dim a_zen(20)
Dim a_go(20)

For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*"　　'61から81を含むセルを指定するための文字列
a_go(i) = ""　　　　　　　　'置換え後はヌルにする。すなわちクリア。
Next i

AAA = Selection.Address
' MsgBox ("セルの座標は、" & AAA & "　です")　　'テスト行

For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i

End Sub
　
　
　置換え元と置換え後文字列を一々指定する場合の文例
　これをテキストファイルで必要な文字列に作っておき、必要な分をエクセルのマクロに貼り付けるといいでしょうね。
　
a_zen(0) = "*" & 61 & "*"
a_zen(1) = "*" & 62 & "*"
a_zen(2) = "*" & 63 & "*"
a_zen(3) = "*" & 64 & "*"
a_zen(4) = "*" & 65 & "*"
a_zen(5) = "*" & 66 & "*"
a_zen(6) = "*" & 67 & "*"
a_zen(7) = "*" & 68 & "*"
a_zen(8) = "*" & 69 & "*"
a_zen(9) = "*" & 70 & "*"
a_zen(10) = "*" & 71 & "*"
a_zen(11) = "*" & 72 & "*"
a_zen(12) = "*" & 73 & "*"
a_zen(13) = "*" & 74 & "*"
a_zen(14) = "*" & 75 & "*"
a_zen(15) = "*" & 76 & "*"
a_zen(16) = "*" & 77 & "*"
a_zen(17) = "*" & 78 & "*"
a_zen(18) = "*" & 79 & "*"
a_zen(19) = "*" & 80 & "*"
a_zen(20) = "*" & 81 & "*"

a_go(0) = ""
a_go(1) = ""
a_go(2) = ""
a_go(3) = ""
a_go(4) = ""
a_go(5) = ""
a_go(6) = ""
a_go(7) = ""
a_go(8) = ""
a_go(9) = ""
a_go(10) = ""
a_go(11) = ""
a_go(12) = ""
a_go(13) = ""
a_go(14) = ""
a_go(15) = ""
a_go(16) = ""
a_go(17) = ""
a_go(18) = ""
a_go(19) = ""
a_go(20) = ""


　交互に並べた文例
　
a_zen(0) = "*" & 61 & "*"
a_go(0) = ""
a_zen(1) = "*" & 62 & "*"
a_go(1) = ""
a_zen(2) = "*" & 63 & "*"
a_go(2) = ""
a_zen(3) = "*" & 64 & "*"
a_go(3) = ""
a_zen(4) = "*" & 65 & "*"
a_go(4) = ""
a_zen(5) = "*" & 66 & "*"
a_go(5) = ""
a_zen(6) = "*" & 67 & "*"
a_go(6) = ""
a_zen(7) = "*" & 68 & "*"
a_go(7) = ""
a_zen(8) = "*" & 69 & "*"
a_go(8) = ""
a_zen(9) = "*" & 70 & "*"
a_go(9) = ""
a_zen(10) = "*" & 71 & "*"
a_go(10) = ""
a_zen(11) = "*" & 72 & "*"
a_go(11) = ""
a_zen(12) = "*" & 73 & "*"
a_go(12) = ""
a_zen(13) = "*" & 74 & "*"
a_go(13) = ""
a_zen(14) = "*" & 75 & "*"
a_go(14) = ""
a_zen(15) = "*" & 76 & "*"
a_go(15) = ""
a_zen(16) = "*" & 77 & "*"
a_go(16) = ""
a_zen(17) = "*" & 78 & "*"
a_go(17) = ""
a_zen(18) = "*" & 79 & "*"
a_go(18) = ""
a_zen(19) = "*" & 80 & "*"
a_go(19) = ""
a_zen(20) = "*" & 81 & "*"
a_go(20) = ""

※もう少し機能アップできますがしつこくなるのでこの辺で終了。
　各自工夫してください。