エクセルＶＢＡの中の OFFSETの記述

質問

早速ですが
Cells(cnt, n - 5) = WorksheetFunction.Sum(Me.Range("cr" & cnt + 1 & ":ct" & cnt + 1))
は　通っています。
sumの範囲を　可変にしたいので
可変値　$CR$6　＝3　とします（ｃｒ、ｃｓ、ｃｔ）
で
Cells(cnt, n - 3) = WorksheetFunction.Sum(Me.Range("cr" & cnt & ":" & Offset("cr" & cnt, $CR$6 * -1, 1)))

が　OFFSETが　定義されていない　となります
こうした、使い方は出来ないのでしょうか

他の方法があればご教示ください

回答No.2

eden3616 · ベストアンサー

訂正です。

上側のコードですが、Addressが抜けておりました。
申し訳ありません。
またMeは不要です。Meはユーザーフォーム等で自身のフォームなどを対象にする場合に用います。
現在のワークシートを対象にするのであれば、Activesheet.Rangeとしてください。

Cells(cnt, n - 3) = WorksheetFunction.Sum( _
　　Range( _
　　　　"cr" & cnt & ":" & _
　　　　Range("cr" & cnt).Offset(Range("CR6") * -1, 1).Address _
　　) _
)

またn及びcntの値は正しいですか？cntが3以下または
nが3以下ですと、エクセルの取り扱えるセル範囲外となりますのでエラーとなります。

以下のテストコードで動作致します。
（合計対象範囲はCR1：CS4となり、結果はCQ4セルに出力されます）

Sub sample1()
Dim cnt As Long, n As Long
cnt = 4
n = 98
Cells(cnt, n - 3) = WorksheetFunction.Sum( _
　　Range( _
　　　　"cr" & cnt & ":" & _
　　　　Range("cr" & cnt).Offset(Range("CR6") * -1, 1).Address _
　　) _
)
End Sub

Sub sample2()
Dim cnt As Long, n As Long
cnt = 4
n = 98
Cells(cnt, n - 3) = WorksheetFunction.Sum( _
　　Range( _
　　　　Cells(cnt, "cr"), _
　　　　Range("cr" & cnt).Offset(Range("CR6") * -1, 1) _
　　) _
)
End Sub

お礼

たびたび　恐縮です

勉強させていただきました。ありがとうございます。感謝

回答No.1

eden3616

CR6セルの値をオフセットの行の移動量として利用したいということでしょうか？

Cells(cnt, n - 3) = WorksheetFunction.Sum( _
　　Me.Range( _
　　　　"cr" & cnt & ":" & _
　　　　Range("cr" & cnt).Offset(Range("CR6") * -1, 1) _
　　) _
)

Rangeはセル範囲のアドレスを「&」で文字列結合しなくても以下のように記述することが出来ます。

Cells(cnt, n - 3) = WorksheetFunction.Sum( _
　　Me.Range( _
　　　　Cells(cnt, "cr"), _
　　　　Range("cr" & cnt).Offset(Range("CR6") * -1, 1) _
　　) _
)

ヘルプの以下の項目を参照してRange及びOffsetの使用方法について確認したほうがよいかと。

■Worksheet.Range プロパティ
式.Range(Cell1, Cell2)

■Range.Offset プロパティ
式.Offset(RowOffset, ColumnOffset)

補足

ありがとうございます。でも
上で　レンジメソッドの失敗
下で　アプリケーション定義エラー
でした。
個別の使い方はヘルプで分かるのですが、こうした、混合型に為ると、正式に習ってない我流が弱みに成ります。