• ベストアンサー
  • すぐに回答を!

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")のようになっている場所には更に””を付けたりするのでしょうか?それも試しましたが、途中の:にも何かしなくてはならないような‥ どなたかご教示願います。

共感・応援の気持ちを伝えよう!

  • 回答数2
  • 閲覧数779
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1
  • end-u
  • ベストアンサー率79% (496/625)

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 『"』(ダブルクォート)文字列をコード出力したい時、 コード内に記述する時は『""』のように2つ重ねます。2つで『"』1コ分です。 数式に『"1号施設"』と記述したい時にコード内では『""1号施設""』としますよね。 コード内の『""1号施設""』を、検索対象文字列としてさらにコード内で記述するには 『""""1号施設""""』とします。 またコード内でインデントしてるでしょうから、その分のスペースも考慮した文字列にしないといけませんね。 (または各行Trimして判断するか) さらに、2行分を検索してますから " 1行目 _" & vbCrLf & " 2行目" と、vbCrLf で繋いで判定しないといけないです。 また、 .ReplaceLine i, "置換文字列1行目" & vbCrLf & "置換文字列2行目" として i 行目1行、に対して2行置換してますから、元コードの直後の行を削除する必要があります。 1行余分に挿入されてますからその分ずれて .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行ですし‥) お手数ですが、今一度、お付き合いねがえませんか?

関連するQ&A

  • 文字列AかBを含むセル数をSUMPRODUCT関数で求める場合

    文字列AかBを含むセル数をSUMPRODUCT関数で求める場合 エクセル2000です。 A1:A10のセル範囲だとして =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1) とすると、同一セルにA、Bの両方があった場合、重複してカウントされてしまいます。 重複を除外するために、 =SUMPRODUCT(ISNUMBER(FIND({"A","B"},A1:A10))*1)-SUMPRODUCT(ISNUMBER(FIND("A",A1:A10))*ISNUMBER(FIND("B",A1:A10))) と、重複してカウントされた数から両方が存在するセルの数を減じて求めましたが、以下のようなより簡素な式でも求められことがわかりました。 =SUMPRODUCT((ISNUMBER(FIND("A",A1:A10))+ISNUMBER(FIND("B",A1:A10))>=1)*1) この式でなぜAB両方があるセルが重複カウントされないのかどうもわかりません。 ご教示いただければ幸いです。 なお、SUMPRODUCTではなく =SUM(COUNTIF(A1:A10,{"*A*","*B*"}))-SUM(COUNTIF(A1:A10,{"*A*B*","*B*A*"})) でもできることは存じておりますが、今回はSUMPRODUCTの疑問として質問いたしました。

  • VBAの複数条件の検索について

    お世話になります。 VBAでEvaluate("SUMPRODUCT((棚卸!$A$1:$A$300=LEFT(C" & i & ",2))*(ISNUMBER(FIND(LEFT(D$1,3),棚卸!$B$1:$B$300)))*(ISNUMBER(FIND(D$2,棚卸!$C$1:$C$300))),棚卸!$F$1:$F$300)")という構文があります。 現在困っていることは、ISNUMBER(FIND(LEFT(D$1,3)のD$1をE$1、F$1、G$1としたいのですが、どのようにすればよいか分りません。 初歩的な質問で申し訳ありませんが、ご教示のほど宜しくお願いいたします。

  • セル内の文章から文字を抜出したいです

    セル内の文章から文字を抜出したいです。 文字の書き方は、本当にランダムな並び方をしています。 [例] A1セル あいうえお12345 ↓ B1セル ○○ A2セル かきくけこ456789 ↓ B2セル ■■ いま、下の関数式を入れていますが、5つしか文字を抜き出せません。 せめて倍の10個は抜出したいのですが、どうすればいいでしょうか? =IF(ISNUMBER(FIND("あいうえお",A2)),"OO",IF(ISNUMBER(FIND("さしすせそ",A2)),"■■",IF(ISNUMBER(FIND("かきくけこ",A2)),"□□",IF(ISNUMBER(FIND("たちつてと",A2)),"◇◇",IF(ISNUMBER(FIND("なにぬねの",A2)),"△△","その他"))))) よろしくお願いいたします。

その他の回答 (1)

  • 回答No.2
  • end-u
  • ベストアンサー率79% (496/625)

>またコード内でインデントしてるでしょうから、その分のスペースも考慮した文字列にしないといけませんね。 インデントのスペースも文字列の前に付けてますか? 一応チェックコードとして 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さんからの回答でしたので、驚きと感動でしたのでお許し下さい。 また、よろしくお願いします。

関連するQ&A

  • EXCEL関数について

    質問があります。 エクセルのA行に 定期点検 (月度検査) ラジオ修理 ボリューム つまみ 校正 視力検査 ボールペン 発注 替芯 購入 コピー用紙 経費 ポケットティッシュ配布用    ・    ・       ・  とあるのを、B行に下記の用に変換したいのですが、 検査 修理 検査 検査 購入 購入 購入 購入 その他 その他 その他    ・    ・       ・  下記のように関数をつくったのですが、うまく動きません =IF(A1="","",IF(ISNUMBER(FIND("点検",A1)),"検査",IF(ISNUMBER(FIND("修理",A1)),"修理",IF(ISNUMBER(FIND("校正",A1)),"検査",IF(ISNUMBER(FIND("検査",A1)),"検査",IF(ISNUMBER(FIND("発注",A1)),"購入",IF(ISNUMBER(FIND("購入",A1)),"購入",IF(ISNUMBER(FIND("経費",A1)),"購入,"IF(ISNUMBER(FIND("配布用",A1)),"購入","その他"))))) わかる方がいらっしゃいましたら、どうぞ教えて下さい。

  • エクセル2002でIF関数を使用して特定の文字列を検索しそれを数字で別のセルに表記する方法

    名に「女子」の文字があれば1の女子。 「短期大学」の文字があれば2の短期大学。「短期」としか書いてないところは3の短期、それら以外は、4のその他。また、女子と短期大学の両方書いてある場合は、最初に出てくる方を採用(例えば○○女子短期大学とあれば1の女子、○○短期大学女子というのであれば、2の短期大学)。このような場合最初の3つの条件であれば=IF(ISNUMBER(FIND("女子",A1)),"1",IF(ISNUMBER(FIND("短期大学",A1)),"2",IF(ISNUMBER(FIND("短期",A1)),3,4))) と考えたんですがその先がわからず。(これもあってるかどうか・・・)もしもわかる方がいましたらぜひぜひ教えてください。

  • エクセルで文字列検索の関数

    エクセルのワークシート関数で質問です。 「A1セルに、文字列、A、B、Cの何れかを含み、かつCDを含まない」ことを調べる関数です。 素直に、 =AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1)),NOT(ISNUMBER(FIND("CD",A1)))) と長ったらしく書けば取得できることはわかるのですが、もっと簡潔なやり方がありそうな気がします。 どうか教えてください。

  • #260の補足です。四捨五入の方法教えて下さい。

    #5362 NO.#2にて、 IF(ISNUMBER(H2),IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,"")   の式を適用すれば、というご回答を頂きましたが、今度、この数式の結果の小数点以下を四捨五入させる必要が発生したため、 以下のように数式を変更しました。 =IF(ISNUMBER(H2),ROUND(IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,""),0) あるいは、 =ROUND(IF(ISNUMBER(H2),IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,""),0) すると四捨五入は出来るのですが、H2セルに数値を入れないと「#VALUE!」が再び出るようになりました。 どうすればいいでしょうか?

  • Excel で初歩的なんですが。

    例えば演算した結果をA列は "○" B列には "×" が表示されています、両方表示されることはありません。該当が無ければどちらも" " 空白になります。 例えば A1:B1 を参照して ○か×か空白 の値をC1に返すにはどうすれば良いでしょうか? =IF(ISNUMBER(FIND とか使っていろいろやってますがエラーばかりで先に進めません。。。 よろしくお願いします。

  • Excel2007で

    Excel2007で =SUMPRODUCT(ISNUMBER(FIND("A",$F18:$F332))*($N18:$N332=H348))+SUMPRODUCT(ISNUMBER(FIND("B",$F18:$F332))*($N18:$N332=H348)) でAという文字が入っているセルで、その行の別のセルがある条件に該当する数とBという文字が入っているセルで、その行の別のセルがある条件に該当する数を計算します。 これとは別に、1つのセルの中で、AないしはBが入っているという条件で、その行の別のセルがある条件に該当する数を計算する場合はどうするのでしょうか?

  • 入力した数値を元にその他の数値を効率よく求める方法

    お世話になります。 Excelの関数について質問させていただきます。 図を見て頂ければ早いかとは思いますが、元の量(A2~A5)を基準に、 現在ある量を入力する事でその他の量が自動で計算できる式を作りたいと思っています。 現時点で試しているのは IF関数を使い、数値の入っているセルを見つける →数値が入っていれば割合を求め数量を出す →数値がなければ次のセルに移動、以下繰り返し 具体的にD2のセルには以下のような計算式が入っています。 IF(ISNUMBER($C$2),$C$2/$A$2*A2,IF(ISNUMBER($C$3),$C$3/$A$3*A2, IF(ISNUMBER($C$4),$C$4/$A$4*A2,IF(ISNUMBER($C$5),$C$5/$A$5*A2, IF(ISNUMBER($C$6),$C$6*B2," "))))) 今は5行しか入っていないので動きますが、実際はもっとある為ネストの制限に引っかかりそうです。 なにより計算式が長くて修正するにも一苦労してしまいます。 ここまで来て行き詰ってしまいました。 既出でしたら申し訳ありません。知恵を貸して下さい。よろしくお願いします。

  • エクセルで検索し別シートに抽出したい。2007

    http://okwave.jp/qa/q6798171.html                                       上記のベストアンサーの方法で試してみましたが上手く行かなかったので、ご教授お願いします。                                                          Sheet1のAからAMにそれぞれ各項目があり、Sheet2はURLベストアンサーの画像と同様になっています。Sheet2の検索項目はO列とP列ですが、後々検索したい項目が増えた時に追加しやすい方法だと助かります。O列は仮名+数字(あ1234)P列は漢字です。                                                              Sheet1のAM2へは下記のようにしました。  =IF(COUNTA(Sheet2!$A$2:$B$2)=0,"",IF(Sheet2!$B$2="",IF(ISNUMBER(FIND(Sheet2!$A$2,B2)),ROW(),""),IF(Sheet2!$A$2="",IF(ISNUMBER(FIND(Sheet2!$O$2,P2)),ROW(),""),IF(AND(ISNUMBER(FIND(Sheet2!$A$2,B2)),ISNUMBER(FIND(Sheet2!$O$2,P2))),ROW(),"")))) Sheet2のA5には下記のようにしました。 =IF(COUNT(Sheet1!$AM:$AM)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$AM:$AM,ROW(A1)))) お手数ですがご教授よろしくお願いします。

  • 関数に関しての質問です

    関数に関しての質問です 現在F列26とG列26のセルを合体させたF26という統合セルがあります。 =SUMPRODUCT((Sheet!$I$1:$I$5000={"茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県"})*ISNUMBER(FIND($AH3,Sheet!$M$1:$M$5000))) 上記のセルを横に引き伸ばした際に、 =SUMPRODUCT((Sheet!$I$1:$I$5000={"茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県"})*ISNUMBER(FIND($AH4,Sheet!$M$1:$M$5000))) 上記のような形で伸びていってほしいのですが、どうすればいいでしょうか。 尚、横に伸ばす先のセルも2つに統合されたものです。

  • excelカレンダーに集計表

    この掲示板にて、カレンダーの作り方を拝見しました。 カレンダー日付の下に毎日累計値を入れていき、 その下に当日分の使用量が出るようにしたいのですが、 未熟者の為、月の変わり目が流動的でうまく式がたてれません。 ご教授の程よろしくお願いします。 今回参考にさせて頂いたカレンダーの元は下記のものです tazsan転用申し訳ありません。 D1に年(2004)、D3に月(12)と入力します。 一行あけてA5からG5までに、日・月・火・・・と曜日を A6 第一週日曜日 =IF(WEEKDAY(DATE($D$1,$D$3,1))=1,1,"") B6 第一週月曜日 =IF(ISNUMBER(A6),A6+1,IF(WEEKDAY(DATE($D$1,$D$3,1))=2,1,"")) C6 第一週火曜日 =IF(ISNUMBER(B6),B6+1,IF(WEEKDAY(DATE($D$1,$D$3,1))=3,1,"")) D6 第一週水曜日 =IF(ISNUMBER(C6),C6+1,IF(WEEKDAY(DATE($D$1,$D$3,1))=4,1,""))   ・   ・  以降、 A7 第二週の日曜日 =G6+1 B7 第二週の月曜日 =A7+1 C7 第二週の火曜日 =B7+1 ・・・という風に、第四週の土曜日までは前日に+1する計算式を書きます。 第五週は最終週なのですこし違います。 A10 第五週の日曜日 =IF(G9+1<=DAY(DATE($D$1,$D$3+1,1)-1),G9+1,"") B10 第五週の月曜日 =IF(ISNUMBER(A10),IF(A10+1<=DAY(DATE($D$1,$D$3+1,1)-1),A10+1,""),"") C10 第五週の火曜日 =IF(ISNUMBER(B10),IF(B10+1<=DAY(DATE($D$1,$D$3+1,1)-1),B10+1,""),"") D10 第五週の水曜日 =IF(ISNUMBER(C10),IF(C10+1<=DAY(DATE($D$1,$D$3+1,1)-1),C10+1,""),"")         ・         ・ これで、年と月を書き換えると日付が自動表示されるようになります