VBAのCodeModule.Linesの記入方法

質問

Set Target = ThisWorkbook
With Target.VBProject.VBComponents("Module2").CodeModule
For i = 1 To .CountOfLines
If .Lines(i, 2) = Range("AE77").FormulaArray = _
"=SUMPRODUCT((ISNUMBER(FIND(""1号施設"",R11C19:R72C19)))*1,((IF(ISNUMBER(範囲1),範囲1,0)+IF(ISNUMBER(範囲2),範囲2,0)+IF(ISNUMBER(範囲3),範囲3,0)+IF(ISNUMBER(範囲4),範囲4,0)+IF(ISNUMBER(範囲5),範囲5,0)+IF(ISNUMBER(範囲6),範囲6,0))>=8)*1)" Then
.ReplaceLine i, Range("AE77").FormulaR1C1 = _
"=ROUNDDOWN((SUMIF(R11C19:R72C19,""*1号施設*"",範囲1)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲2)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲3)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲4)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲5)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲6))/8,0)"
End If
Next i
End With

の
If .Lines(i, 2) = 　　　
以下の
Range("AE77").FormulaArray = _
"=SUMPRODUCT((ISNUMBER(FIND(""1号施設"",R11C19:R72C19)))*1,((IF(ISNUMBER(範囲1),範囲1,0)+IF(ISNUMBER(範囲2),範囲2,0)+IF(ISNUMBER(範囲3),範囲3,0)+IF(ISNUMBER(範囲4),範囲4,0)+IF(ISNUMBER(範囲5),範囲5,0)+IF(ISNUMBER(範囲6),範囲6,0))>=8)*1)" 　Then

記入方法が解りません。

605行目からは2行にわたるコードを記載してるのですが、Range("AE77").FormulaArray = 以下>=8)*1)"まで（605行以外にもあるのでForで廻してます）

文字列として””で括るるんだろうなと思ってトライしましたが、””で括る場所が多すぎてどこに付ければいいのやらです。最初と最後を””付けるだけでなく元々("AE77")のようになっている場所には更に””を付けたりするのでしょうか？それも試しましたが、途中の：にも何かしなくてはならないような‥
どなたかご教示願います。

回答No.1

end-u · ベストアンサー

Dim s As String
s = " Range(""AE77"").FormulaArray = _" & vbCrLf & _
" ""=SUMPRODUCT((ISNUMBER(FIND(""""1号施設"""",R11C19:R72C19)))*1,(" & _
"(IF(ISNUMBER(範囲1),範囲1,0)+IF(ISNUMBER(範囲2),範囲2,0)" & _
"+IF(ISNUMBER(範囲3),範囲3,0)+IF(ISNUMBER(範囲4),範囲4,0)" & _
"+IF(ISNUMBER(範囲5),範囲5,0)+IF(ISNUMBER(範囲6),範囲6,0))>=8)*1)"""
MsgBox s
s = " Range(""AE77"").FormulaR1C1 = _" & vbCrLf & _
" ""=ROUNDDOWN((SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲1)" & _
"+SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲2)" & _
"+SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲3)" & _
"+SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲4)" & _
"+SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲5)" & _
"+SUMIF(R11C19:R72C19,""""*1号施設*"""",範囲6))/8,0)"""
MsgBox s

『"』（ダブルクォート）文字列をコード出力したい時、
コード内に記述する時は『""』のように２つ重ねます。２つで『"』１コ分です。
数式に『"1号施設"』と記述したい時にコード内では『""1号施設""』としますよね。
コード内の『""1号施設""』を、検索対象文字列としてさらにコード内で記述するには
『""""1号施設""""』とします。

またコード内でインデントしてるでしょうから、その分のスペースも考慮した文字列にしないといけませんね。
（または各行Trimして判断するか）

さらに、２行分を検索してますから
" １行目 _" & vbCrLf & " ２行目"
と、vbCrLf で繋いで判定しないといけないです。

また、
.ReplaceLine i, "置換文字列１行目" & vbCrLf & "置換文字列２行目"
として i 行目１行、に対して２行置換してますから、元コードの直後の行を削除する必要があります。
１行余分に挿入されてますからその分ずれて
.DeleteLines i + 2
です。

しかしながら、Module内コードをマクロから書き換える操作はおすすめしません。
事情があるのでしょうけど、そもそもの仕様から見直すようにされたほうが良いでしょう。
恐らく多数のユーザーに配布してしまったBookの書き換えの為でしょうけど、
そういった配布の問題については、マクロブックとデータブックの分離、アドインの導入、
など検討されたほうが良いかと思います。
http://www.asahi-net.or.jp/~ef2o-inue/haifu/sub06_010.html

お礼

最新の回答で気付かされましたが、すでにこの回答時で完璧だったのですね。
ありがとうございました。

補足

end-uさん。お久しぶりです。1年くらい前によくここで質問していたのですが、その時も何度も助けて頂きました。今回も早速の回答ありがとうございます。
さて、試させていただいたところ。
まさにMsgBox には寸分たがわぬ式が表示されます。
そこでs =以下をIf .Lines(i, 2) =以下にそのまま貼り付けてみてテストしましたが、ヒットしません。スルーされてしまいます。
そこでそれ以前の流れの検証のため、式を簡略しIf .Lines(i, 1) ="A = B"
として適当なところにA=Bと記入しヒットするか検証し、結果問題なくヒットしました。
よって同一の文字列と見ていないということになるのでしょうが、しっかり記入してるのを確認してます。
If .Lines(i, 2) = 　　　←　　辺りに問題あるのでしょうか？（でも間違いなく2行ですし‥）
お手数ですが、今一度、お付き合いねがえませんか？

回答No.2

end-u

>またコード内でインデントしてるでしょうから、その分のスペースも考慮した文字列にしないといけませんね。
インデントのスペースも文字列の前に付けてますか？

一応チェックコードとして
Sub test()
　　Dim s As String
　　Dim i As Long
　　With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
　　　　For i = 1 To .CountOfLines
　　　　　　If Trim(.Lines(i, 1)) = "Range(""AE77"").FormulaArray = _" Then
　　　　　　　　Debug.Print .Lines(i, 2)
　　　　　　　　Debug.Print "■検索文字↓■"
　　　　　　　　Debug.Print """" & Replace(.Lines(i, 1), """", """""") & """" & " & vbCrLf & _"
　　　　　　　　Debug.Print """" & Replace(.Lines(i + 1, 1), """", """""") & """"
　　　　　　End If
　　　　Next
　　End With
End Sub
うまくいけば[イミディエイトウィンドウ]に検索文字が書き出されます。
それを使ってみてください。

お礼

>またコード内でインデントしてるでしょうから、その分のスペースも考慮した文字列にしないといけませんね。

　見落としてました。まさにそこでした。
自分なりにDebug.Printしてみてたりしてたのですがスペースを考慮させてない表示でしたので、それをみてもまったく同じ式なのに‥と悩んでました。
ついでに、スペースを反映させるデバックのコードまで示して頂き大変助かります。
実は、さらに類似の処理も続くので、スペース数えたりする手間が省けました。
また、考えかたもご教示頂き、いつもながら頭が下がります。
まさに痒いところに手が届く完璧な回答でした。

助かりました。本当にありがとうございます。
end-uさんはいろんな方に回答なされているでしょうから覚えてないとは思いますがyokokama46は昔end-uさんに本当に助けて貰ったのを覚えています。
また、一年振り以上なのでここのデザインも変わったことに少しとまどいながらも、ken-kenSPさんやredfox63さんにもお世話になってたことも思い出したりしました。
関係ない話で恐縮ですが、久しぶりの質問に、一番お世話になったend-uさんからの回答でしたので、驚きと感動でしたのでお許し下さい。
また、よろしくお願いします。