[[20161024152404]] 『数式の入力場所をOffset(最下段)に表示しないで』(はんりん) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『数式の入力場所をOffset(最下段)に表示しないで表の最上段(A4)に表示させたい。』(はんりん)

はじめまして、VBAの勉強中です。
表の上部5行ぐらいが表題等で空いているので、この部分に計算式を入れて個別データの集計をと考えております。
下記のようにした場合、表の最下段の下空白に計算式が入りますが、この計算式をA4に入るようにしたいのです。
Offset(1,0)をRange、Cellsなどにしてみましたがエラーとなり表示しません。
ご教示の程お願い出来ますでしょうか。

With Range("A7") 'Aの欄

         .End(xlDown).Offset(1, 0) = _
          "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"
    End With

< 使用 Excel:Excel2010、使用 OS:Windows7 >


>.End(xlDown).Offset(1, 0)

.Offset(-3, 0)

にしてください。

EndプロパティとOffsetプロパティの意味を理解しましょう。。。

参考URL>>
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html
(まっつわん) 2016/10/24(月) 15:41


あ、

.Offset(-3).Formula = "=A1+C1"

数式を入れるのにFormulaプロパティを明示しないのはお行儀が悪いかと思います。

というか、固定の位置なら最初から数式を手入力しておけばいいかと。。。^^;
(まっつわん) 2016/10/24(月) 15:46


細切れにごめん。

数式の参照範囲が可変の場合のやり方↓

http://excel-ubara.com/excel3/EXCEL016.html
http://d.hatena.ne.jp/iroiro-memo/20140906/1410792047
(まっつわん) 2016/10/24(月) 15:51


まっつわん様
早速のご教示ありがとうございます。
このようにして、お聞きするのは全く初めてですので、要領を得ません。
質問の仕方が悪く申し訳ございません。
With Range("A7") 'Aの欄
         .End(xlDown).Offset(1, 0) = _
          "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"
    End With
A7〜データの最終行を選択してSUM関数を利用して合計を出す。数式にしているつもりですが、
A1〜A5の5行が空欄となっておりますので、その部分に集計する数式を入れることができないかと考えております。

最初のご教示では.Offset(-3, 0) 最終行から3行上を指定されているのでは?
本を見ても中々こんがらがって理解できていません。
ご教示の程宜しくお願いします。

(はんりん) 2016/10/24(月) 16:23


>質問の仕方が悪く申し訳ございません。
いや、ご自分の問題なので何度でも聞き直して、納得してください。
こちらの言葉が足りない部分があったのでしょう。

>最初のご教示では.Offset(-3, 0) 最終行から3行上を指定されているのでは?

いやいや、最終行は、
.End(xlDown)で探しているので
それは不要でいいですよね?

で、

>With 'Aの欄
> .End(xlDown).Offset(1, 0) = _

上は、
Range("A7").End(xlDown).Offset(1, 0) = _
の意味なので、
Range("A7").Offset(-3, 0) = _
とすれば、A4の位置が求まりますよね?
と書いたつもりでしたが意図が伝わらなかったですかね?

With句の意味が分かり難い場合は、使わない方がいいかもです。

(まっつわん) 2016/10/24(月) 17:16


まっつわん様

ご丁寧にご教示頂きありがとうございます。

私の理解が足りないのか、OffsetではA4の位置が決まりませんでしたので
With Range("A7")

         .End(xlDown).Offset(1, 0) = _
          "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"
     Range("A4").Formula = ActiveCell.Formula
    End With

Range("A4").Formula = ActiveCell.Formula・・・でA4の位置に数式を移動することができました。
還暦を過ぎてからのVBAの勉強は中々物事を柔らかく考えることができなくなっております。
今後とも、何で?どうして?のご教示をお願い致します。
何卒宜しくお願いします。
ありがとうございました。 拝
(はんりん) 2016/10/25(火) 09:25


 まっつわんさんの回答にあるURL先読みました?
 Range("A7").Offset(-3, 0)
           ↑ ↑
           行 列

 行の部分を-3にすることでA7セルを基準に3行上のA4セルが指定される。
 わざわざA4セルを指定するマクロを入れなくてもまっつわんさんが書いたように

 With Range("A7")
     .Offset(-3, 0) = "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"
 End With

 でできるはずです。
(bi) 2016/10/25(火) 10:39

>私の理解が足りないのか、OffsetではA4の位置が決まりませんでしたので

うぉ。
伝わりませんかぁ。。。
色とかここ使えませんでしたっけねぇ。。。。

With Range("A7")

    .offset(-3,0).select
end with

これで、A4セル選択できませんかね????
とにかく、新しくプロシージャ作って実験して確認するようにしてください。
そうすることで覚えられると思うのですが。。。
誰かが書いたコードの一部を傾向と対策でちょいちょい直して使えると思ったら大間違いです。

まぁ、決め打ちなら

Range("A4").Formula = "=SUM(" & Range(.Address, .End(xlDown)).Address(False, False) & ")"

で、いいですね。

ActiveCellは出来るだけ使わない方がいいです。
まちがってマウスとかでセルを触ったら万が一ですが、
Activeなセルが意図しないセルに変わる可能性がないとは言い切れないので。。。

(まっつわん) 2016/10/25(火) 10:58


まっつわん様
bi様

With Range("A7")         With Range("A7")

     .End(xlDown).Offset(-3, 0) ⇒ .Offset(-3, 0) にしたらよかったのですね!
やっとわかりました! .End(xlDown)の後にOffsetを使用したのがいけなかったのですね。
ご両者様 ありがとうございます。
ActiveCellの使用注意承知致しました。
セルを置いたところで実行させると全然違う結果が出ることがありましたので・・・そういう事なんですね。

大変お世話になり申し訳ございませんでした、勉強になりました。
ありがとうございます。

別にSUBTOTAL関数を使ったマクロを作ろうとしております。また一から本と格闘してみます。
今後も宜しくお願いします。
(はんりん) 2016/10/25(火) 15:13


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.