[[20201031033252]]　『最終行を　Ｚ＋２　と置いたときの書き方を教えて』（老眼オジサン）　ページの最後に飛ぶ

『最終行を　Ｚ＋２　と置いたときの書き方を教えてください。』（老眼オジサン）

シ－ト名を複合参照　工事番号をＡ１６から約１００行記載し、最終行までの入力件数をＡ１４にＣＯＵＮＴ関数で表示し、当該数値を　Ｚ　として（Cells(Rows.Count.2).End(xlUP).ow を使用せず）Dim As Long 改行　Ｚ=Seets("複合参照”）.Range("A14").Value +15 と置いた場合に、If($A16>0,Index($A16:A$100,Match($A16,$A$16:$A$100,0)),"")という固定範囲ではなくA100に該当する部分に　Ｚ　という文字を代替して記載する方法を教えてください。変数が1個所の場合にはRange("A14")Formula="=Counta($A16:$A$" & Z+0 & ")"のように単純式で大丈夫なのですが、上記のような複数個所に変数を使用する場合の記載方法が、恥ずかしいのですが、解りません。複数個所の変数を記載する方法についてお教えください。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 数式に限らず文字列と変数を結合するのは、複数でも同じです
"文字列" & 変数 & "文字列" & 変数 & "文字列" & ･･･
それより、最後のダブルコーテーション(")を文字列として表示する
方が少し厄介だと思います
参考:
http://officetanaka.net/excel/vba/tips/tips90.htm
件数を求めるには、セルにCOUNT関数をいれなくても
WorksheetFunction.Countを使用する事も出来ます

（はまちゃん） 2020/10/31(土) 07:02

朝方早々に御教授有り難うございました。

"文字列" & 変数 & "文字列" & 変数 & "文字列" & ･･･の部分を具体的にＡ１６とＺとを含めて実際の算式形式で教えてください。

なお参考部分は変数ではなく文字列と””との関連部分かなと思います。ＣＯＵＮＴ関数ではなくWorksheetFunction.Countは後刻挑戦してみます。

（老眼オジサン） 2020/10/31(土) 10:17

横から失礼します。
ひとつでできるのでしたら、二つでもいくつでも同じようにできませんか？
具体的に回答されていると思いますが、どのあたりが不明ですか？
人の回答をコピペしようと待ち構えるのでなく、
ご自分のトライでうまくいかなかったら、それを示してみたらどうでしょうか？

（γ） 2020/10/31(土) 10:24

（γ）さん有り難うございます。　使用中の算式は下記としてあります

途中で　Range("A14").Formula = "=COUNTA($A16:$A$1000)"　Dim　ｚ　As　Long　と置いてあり

Range("CA14").Formula = "=IF(A$14>0,INDEX(A$14:A$ " & z + 2 & ",MATCH($A14,$A$14:A$ " & z + 2 & ",0)),"")"　と記載してありますが、実行時エラ－1004　と表示されます。

何処を修正したら通常に計算されるのかを教えてください。

拝

（老眼オジサン） 2020/10/31(土) 12:27

はまちゃんさんのコメントにあるリンク先を確認しましたか

（マナ） 2020/10/31(土) 12:49

代入する前に、

    s = "=IF(A$14>0,INDEX(A$14:A$ " & Z + 2 & ",MATCH($A14,$A$14:A$ " & Z + 2 & ",0)),"")"
    Debug.Print s  'イミディエイトウインドウに出力
として、その式が適正なものか確認してください。
どこがまずいか確認する手法も大切です。

（γ） 2020/10/31(土) 13:01

（マナ）さんへ　当該サイトは文字列と””との説明でした故に対象外と思います。最初の返信部分に記載しておりますが、直接該当ではないと判断しました。

（γ）さんへ　当該算式が代入されているマクロ画面から　表示　　イミデントウィンドウを押して　マクロ画面の下に　イミディエイト　ブロックが表示されるので、当該ブロック画面内に　s = "=IF(A$14>0,INDEX(A$14:A$ " & z + 2 & ",MATCH($A14,$A$14:A$ " & z + 2 & ",0)),"")"
Debug.Print s　と記入しエンタ－キ－を押しましたが　コメントは表示されません。小文字ｓの前行には何も記載してありません。他のインタ－ネットでは　ｓの前に？を置いて数式の最終部分でリタ－ンキ－を押すとありましたので当該処理を行いましたがコメントは表示されません。
イミディエントウィンドウ自体が初めての経験で八幡の藪知らず状態でございます。
また　マクロ文の中に当該ｓ以降を置いてデバック処理を行った場合には何ら止まらずにEndSubまで進んで終了しますが文中にsを付けない場合には当然でしょうが従前通りに停止してしまいます。
恐縮ですが Debug.Print s 以降の必要な処理方法を教えてください。
幾度も発信しまして申し訳ございません。

（老眼オジサン） 2020/10/31(土) 14:49

 (1)
 まず、紹介があったサイトは、すごく関連していますよ。
 ダブルクォーテーションの中でダブルクォーテーションを使う場合（これに該当します）には
 特別の作法が必要になります。そのことを説明したものです。
 そこが理解できていないと、このテーマは前に進みません。
 素直に読んで下さい。
 また、結果だけもらっても身につきません。

 (2)
 Sub test()
     Dim Z As Long
     Dim s As String

     Z = 100         '仮の数値です。テストが済んでからそちらで修正してください。
     s = "=IF(A$14>0,INDEX(A$14:A$ " & Z + 2 & ",MATCH($A14,$A$14:A$ " & Z + 2 & ",0)),"")"
     Debug.Print s
     ''' Range("A1").Formula = s
 End Sub

 これを実行してみて下さい。
 イミディエイトウインドウはあなたの画面で表示されているんですか？
 メッセージボックスではなく、イミディエイトウインドウに文字列が表示されるはずです。
 それが式として妥当なものか見て下さい。

（γ） 2020/10/31(土) 15:03

 >イミディエントウィンドウ自体が初めての経験
で八幡の藪知らず状態でございます
ならば、VBAではなく手入力でセルに
A1=""
を文字列で返す数式を考えてみては？
="A1=
この後"はいくつ必要なのか･･･?

（はまちゃん） 2020/10/31(土) 15:34

testを実行すると、イミディエイトウインドウにこう出力されませんか？

=IF(A$14>0,INDEX(A$14:A$ 102,MATCH($A14,$A$14:A$ 102,0)),")

式の内容は関知しませんが、少なくとも以下が指摘できます。
(1)A$ 102 と余計なスペースが入ってしまっている。
(2)A$14>0　が成立しないときに、 " となってしまっている。
　　（本来は""であるはず。）
　　
(2)については、紹介されたサイトにあるように　"を二つ続けることで
はじめてひとつの"になりますから、
結局、
"=IF(A$14>0,INDEX(A$14:A$" & Z + 2 & ",MATCH($A14,$A$14:A$" & Z + 2 & ",0)),"""")"
とすることになるのではないですか？
# 繰り返しますが、式の妥当性は除外しています。
（γ） 2020/10/31(土) 19:57

はまちゃん様　γ様　Why様　有り難うございました。
下記算式で無事工事番号表示のＣＡ列には工事番号（Ａ列値）が表示されました。ＣＢ列以降は＃Ｎ／Ａと表示されていますがサンプル行最終値までは各列同様の算式がエクセルの式表示窓に置かれていました。これからINNDEXとMATCHとの中をチェックしてみます。
Range("CA16:CG$" & Z & "").Formula = "=IF($A16*1>0,INDEX($A$16:A$" & Z & ",MATCH($A16,A$16:A$" & Z & ",0)),"""")"
Ｂ列は工事名称で文字列Ｃ列以降は各工事原価で数値を置いてありますが、チェック作業で注意しなさいよという御指示がございましたらお教えください。
ＣＡ列に工事番号が番号順に正しく表示されましたので一安心しており又朝方から頑張って見ます。
素人相手に御配慮を多々賜りまして本当に有り難うございました。
拝

（老眼オジサン） 2020/11/01(日) 06:07

 >MATCH($A16,A$16:A$" & Z & ",0))
A16の値はA16にあるので、Z値に関係なく1しか返らない
CB列以降は列が相対参照なのでエラーになる
複数の列を返したいのに、INDEX関数の列番号がない
 >Range("CA16:CG$" & Z & "").Formula =
& ""を付けた理由は？

（はまちゃん） 2020/11/01(日) 07:29

Sub test()

    Dim z As Long

    z = Range("A14").Value
    Range("CA16").Resize(z, 7).Formula = "=A16"
End Sub

こういうこと？
違うか＾＾；
（まっつわん） 2020/11/01(日) 12:03

 数式の文字列の作り方の質問かと思っていたんですが、
 その数式で実現したい実務内容にも踏み込んだのですか？

 なら、そのやりたい事の説明をしてもらった方がいいです。

 ※前回の質問内容と殆ど変わってない様に見受けられますが、
 　バージョンがOffice365なのに、なぜ旧式の式(？）に拘るんですかねぇ。。

（半平太） 2020/11/01(日) 12:15

そうですね、私も式のおかしさに気づいてはいたがあえて避けていた。
だから何度も内容は除外と書きました。

あえて聞きますがダブルクォーテーションを含む
文字列の取り扱いは理解されたんですか？
何のコメントも無いですけど。(マナさんも指摘されていたのですよね)
ご自分の言葉で理解を確認された方がいいですね。
結局はこういう些細なことの積み重ねなので。

またイミディエイトウインドウへの入力は一行ごとに
エンターが必要です。sは何もセットされて無かったのでしょう。
指摘が後先になって恐縮です。

なお、普通は計算式がきちんと確定してから
それをマクロ化します。
場合によってはマクロ記録を取って確認することも有ります。
（γ） 2020/11/01(日) 12:46

多々御教授有り難うございました。
ＢＫ列１６行以降に完成工事の工種毎内訳　ＢＬ列１６行以降に各完成工事額
ＢＭ列１６行以降に完成工事の集計用工種別名を記載し、ＢＮ列にSUMIF関数で対象売上高を集計する形で無事終了いたしました。
Range("BN14").Formula = "=SUM(BN16:BN" & Z + 0 & ")"
Range("BN16:BN" & Z + 0).Formula = "=+IF(AND(A16>0,LENB(BM16)>0),SUMIF(BK16:BK" & Z + 0 & ",BM16,BL16:BL" & Z + 0 & "),"""")"
Range("BN16").Select
算式自体は正式形式か否かは不明ですが、各工事別内訳が工種毎額に集計されています。
算式を確定させてから変数に置換する形で完了という経緯でございました。
オフィス３６５での数式ではないかと危惧しておりますが、参考書購入して何処が変更になっているか調べてみようと思います。
多々御迷惑を掛けまして申し訳ございませんでした。また末尾になりましたが本当に有り難うございました。
拝

（老眼おじさん） 2020/11/04(水) 09:05

 IndexとMatchの組合せではなかった、と言うことは納得です。
 新たな案もちょっとピンと来ないですけど、結果オーライなんでしょうね。

 念の為、最終行の近くのセルの数式が、意図した通りであることを確認していただくといいかもです。

 ＞オフィス３６５での数式ではないかと危惧しておりますが、
 正しい答えが出ているなら、危惧する必要はありません。
 折角の強力な機能を使わなかった、と言うだけのことです。

 ＞参考書購入して何処が変更になっているか調べてみようと思います。
 変更と言うより、新機能と言う方が近いです。

  常に進化しているので、お手持ちの本に解説が載っているかどうかは分からないです。
 「スピル機能」と言う用語が見当たらなければ、カバーされてないです。

（半平太） 2020/11/04(水) 11:48

コメント返信：

[ 一覧(最新更新順) ]

[[20201031033252]] 『最終行を Ｚ＋２ と置いたときの書き方を教えて』（老眼オジサン） ページの最後に飛ぶ

[[20201031033252]]　『最終行を　Ｚ＋２　と置いたときの書き方を教えて』（老眼オジサン）　ページの最後に飛ぶ