EXCEL VBAコミュのセルの範囲指定を計算式で表したい。

お世話になっております。
また　よろしくお願い致しますm(_ _)m

セルを範囲指定する時に
計算式を使いたいのです。

Dim i As Integer, c　As Integer

と指定しておいて
範囲指定を　a1セルから　i行分下を範囲指定したいのです。

イメージとしては
Range（"a1+i : i50+i").Select
としたいのですが　これではエラーになってしまいます。
この様な場合　どのように表記すればいいのでしょう？

また　VBAの中で　他のBOOKを開いてSheetを順に読ませたいのですが
その時に　一番左のSheetを（Sheet名はその時にならないとわからない）アクティブにする事は可能でしょうか？

コメント(12)

最初
全て
最新の40件

[1] mixiユーザー 06月02日 12:28

> 範囲指定を　a1セルから　i行分下を範囲指定したいのです。

それなら、Offsetを使うといいです。
↓を試してみてください。
Sub Test1()
　　Dim i As Long
　　i = 10
　　MsgBox Range("A1").Offset(i).Address
End Sub

> また　VBAの中で　他のBOOKを開いてSheetを順に読ませたいのですが
> その時に　一番左のSheetを（Sheet名はその時にならないとわからない）
> アクティブにする事は可能でしょうか？

可能です。
↓のようにします。
Sub Test2()
　　Dim bkA As Workbook
　　Dim shtA As Object　’コレが目的のシート

　　Set bkA = Workbooks.Open("D:\Book1.xls")
　　Set shtA = bkA.Sheets(1)
　　MsgBox shtA.Name
End Sub

シートに対して順番にアクセスするなら、
Sub Test3()
　　Dim bkA As Workbook
　　Dim shtA As Object

　　Set bkA = Workbooks.Open("D:\Book1.xls")
　　For Each shtA In bkA.Sheets
　　　　MsgBox shtA.Name
　　Next shtA
End Sub
といった感じです。

なお、アクティブにしなくてもシートのデータは読めます。

[2] mixiユーザー 06月02日 12:36

ビリー様
早々の回答ありがとうございました。

Offsetって手があったのですね！！
テキストで勉強して言葉を知っていても
どういう場面で使えばいいのかが　まだ理解出来てないようです。
ずっとこれで悩んでいて　ビリー様の回答をみた瞬間
“あああああ～～～！！！”
って叫んでしまいました。

一番最初のSheetにアクセスする方法も
ネットで調べても使い方がイマイチわからず
構文（？）で書いて頂いて助かりました。

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

[3] mixiユーザー 06月02日 13:11

dim i as integer

i=10
Range（"A1:A" & i).Select

という方法もあります。

[4] mixiユーザー 06月02日 13:31

範囲なら、Offsetじゃなくて、Resizeを使うべきでしたね。
Sub Test1R()
　　Dim i As Long
　　i = 10
　　MsgBox Range("A1").Resize(i).Address
　　MsgBox Range("A1").Resize(i + 1).Address
End Sub

[5] mixiユーザー 06月02日 15:48

autumn様
ビリー様　ありがとうございます。

色々と試してみたのですが　何故か旨くいきません。

元データBookにSheetが複数枚あって
Sheet1にA商店のデータ　Sheet2にB商店のデータが・・となっています。

Sub Test3()
　　　　Dim bkA As Workbook
　　　　Dim shtA As Object
　　　　Dim c As Integer

　　　　Set bkA = Workbooks.Open("D:元データ.xls")

　　　　c = 1
　　　　For Each shtA In bkA.Sheets
　　　　　　shtA.Select
　　　　　　コピー2 c
　　　　　　c = c + 50
　　　　　　Windows("元データ.xls").Activate
　　　　Next shtA

End Sub

というメインルーチンを作って

Sub コピー(cnt As Integer)
　　　　Range("A3:i50").Select
　　　　Selection.Copy
　　　　Windows("集計.xls").Activate
　　　　Range("A1:A" & cnt).Select
　　　　ActiveSheet.Paste
End Sub

としたら　移動先のデータを置き換えますか？と出て
集計Bookには　元データの最終シートの値だけが残ります。

Sub コピー2(cnt As Integer)

　　　　Range("A3:i50").Select
　　　　Selection.Copy

　　　　Windows("Book1.xls").Activate
　　　　 Range("A1").Resize(cnt).Activate
　　　　ActiveSheet.Paste
End Sub

としても最終Sheetのデータだけが集計Bookにコピーされた状態です。

なぜなんでしょう？

[6] mixiユーザー 06月02日 23:07

選択したりアクティブにしたりしたからといって、黙ってRangeと書けば、アクティブシートのセルを指すとは限りません。
コピーや貼り付けをするときに選択やアクティブにしなくても済む書き方を覚えてください。

Sub Test3()
　　Dim bkA As Workbook
　　Dim shtA As Worksheet
　　Dim c As Long

　　Set bkA = Workbooks.Open("D:\元データ.xls")

　　c = 1
　　For Each shtA In bkA.Worksheets
　　　　Call SubCopy(shtA, c)
　　　　c = c + 50
　　Next shtA
End Sub

Sub SubCopy(ByRef Arg1 As Worksheet _
　　　　　, ByVal Arg2 As Long)
　　Arg1.Range("A3:I50").Copy _
　　　　Destination:=ThisWorkbook.Worksheets(1).Cells(Arg2, "A")
End Sub

[7] mixiユーザー 06月03日 01:29

ビリー様
あっという間に出来ました。

何日もずっと悩んでいたのが
こんなに数行で解決するとは思いませんでした。

テキスト１冊じゃまだまだですね・・・

アドバイスありがとうございました。
ありがとうございました。

[8] mixiユーザー 06月05日 23:36

すみません。。。

まだ悩んでおります。

集計Ｂｏｏｋに貼り付けるときに
Sheet名も貼り付けたいのですが・・・

でないと　どの支店のデータかわからなくなってしまいます。

Sub SubCopyをCallするときに　色々と試してみたのですが旨くいきません。

再度　ご教授お願いできますでしょうか？

[9] mixiユーザー 06月05日 23:46

SubCopyプロシージャには、Arg1 As Worksheet で
コピー元のシートを渡しているので、Arg1.Nameで
そのシート名が得られます。

で、これをどこに使うんでしょう？

[10] mixiユーザー 06月06日 00:00

ビリーさま
何度も申し訳ございません。

コピー元のデータ"A3:I50"を集計データに貼り付けるときに
集計シートのJ列に貼り付ければ　
貼り付け後にピボットを組んでも　支店名がわかるのですが・・・

元データを開く前に　一度開いて　コピーして再保存も試したのですが
なぜか　変更が保存されませんでした。

集計データにコピー後に貼り付けを試しても
貼り付ける位置の特定が出来ませんでした。

Arg1.Nameでシート名がわかるんですね。
もうちょっと頑張ってみます。
ありがとうございました。

[11] mixiユーザー 06月06日 00:09

参考までに。
　　ThisWorkbook.Worksheets(1).Cells(Arg2, "J").Resize(48).Value _
　　　　= Arg1.Name

# 眠いんで、いい加減です。

[12] mixiユーザー 06月06日 00:20

ビリー様
遅い時間までお付き合いいただいて
申し訳ありませんでした。

完璧です。

本当にありがとうございました！！！

ログインすると、みんなのコメントがもっと見れるよ