[[20130628101456]]　『R1C1方式で数式を挿入した際に＄を外したい』（ねむ）　ページの最後に飛ぶ

『R1C1方式で数式を挿入した際に＄を外したい』（ねむ）

 VBAでデータシートの集計をするマクロを作りました。
 参考にしたのは↓のサイトです
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_030_060.html

 Public lsh As Worksheet

 Private Sub 集計()
    Dim GYO1 As Long
    Dim GYO2 As Long
    Dim GYO As Long
    Dim strFORMULA As String
    Dim kei As Variant
    Dim z As Long

    ' 1行目から処理開始(見出しなし)
    GYO = 4

    Application.ScreenUpdating = False

    Do While lsh.Cells(GYO, 1).Value <> ""
        ' 小計ｸﾞﾙｰﾌﾟの先頭行→GYO1
        GYO1 = GYO
        GYO = GYO + 1
        ' 次の行から同じｸﾞﾙｰﾌﾟでない行を見つける
        Do While lsh.Cells(GYO, 12).Value = lsh.Cells(GYO1, 12).Value
            GYO = GYO + 1
        Loop
        ' 同じｸﾞﾙｰﾌﾟの最終行→GYO2
        GYO2 = GYO - 1

        ' 小計行を挿入
        Rows(GYO).Insert
        lsh.Range("A" & GYO & ":B" & GYO).Merge
        lsh.Cells(GYO, 1).Value = lsh.Cells(GYO - 1, 12).Text & "小計"
        lsh.Cells(GYO, 1).Font.Bold = True
        lsh.Cells(GYO, 1).HorizontalAlignment = xlRight

        '受注数合計
        lsh.Cells(GYO, 8).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO1 & "C8:R" & GYO2 & "C8)"
        lsh.Cells(GYO, 8).Font.Bold = True
        '出荷数合計
        lsh.Cells(GYO, 9).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO1 & "C9:R" & GYO2 & "C9)"
        lsh.Cells(GYO, 9).Font.Bold = True
        '売上額合計
        lsh.Cells(GYO, 16).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO1 & "C16:R" & GYO2 & "C16)"
        lsh.Cells(GYO, 16).Font.Bold = True

        GYO = GYO + 1
    Loop
 End Sub

 これで、lshシートのL列をキーにしてH、I、P列の小計を出すようにしました。

 すると

 =SUBTOTAL(9,$H$4:$H$13)

 というように数式が入るのですが、この$マークを入れたくないのです。
 （最低でも行数の部分は$を外したいです）

 =SUBTOTAL(9,H4:H13)
 または
 =SUBTOTAL(9,$H4:$H13)

 とするにはどうしたらいいでしょうか。

 Excel2010です。お願いします。

 "=SUBTOTAL(9,R[" & GYO1 & "]C[8]:R[" & GYO2 & "]C[8])"
 "=SUBTOTAL(9,R[" & GYO1 & "]C8:R[" & GYO2 & "]C8)"
 BJ

 本来であれば R[xx]C[xx] で相対指定すればよいのでしょうけれど、範囲が都度変わるよう
 なので式設定後に
        lsh.Cells(GYO, 8).Formula = Replace(lsh.Cells(GYO, 8).Formula, "$", "")
 のように置換して回避できないでしょうか。
 でも ＄ があると何か困るのですか？
 (Mook)

         lsh.Cells(GYO, 8).Formula = _
        Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C8:R" & GYO2 & "C8)", xlR1C1, xlA1, 4)
 (seiya)

 BJ様、Mook様、seiya様ありがとうございます。

 Bj様の
 "=SUBTOTAL(9,R[" & GYO1 & "]C[8]:R[" & GYO2 & "]C[8])"

 にするとなぜかH列にP列の集計、I列にR列の集計が入ったりしてしまったのですが、下の段の数式だと大丈夫でした。

 ＞Mook様

 集計表を作った後別ブックに保存して、そこで新たに行を挿入したり削除したり、ということがあるので、$がついて数式が固定されると
 その都度修正しなければならないので…
 （事情があって集計元の数値は変えずに集計後のデータのみ操作するケースが多々あります）

 seiya様のコードでも大丈夫でした。

 皆様ありがとうございました！

 （ねむ）

 すみません、BJ様の下の段のコードで１つ目の小計グループはよかったんですが２つ目以降のグループから行数が
 激しくずれてしまいます…
 ずれる規則性が分からないので謎です…

 時間ができた時に色々試してみます。

 （今のところseiya様のコードでうまく行っています）

 （ねむ）

コメント返信：

[ 一覧(最新更新順) ]

[[20130628101456]] 『R1C1方式で数式を挿入した際に＄を外したい』（ねむ） ページの最後に飛ぶ

[[20130628101456]]　『R1C1方式で数式を挿入した際に＄を外したい』（ねむ）　ページの最後に飛ぶ