エクセル VBA オフセットさせたいのですが。

質問

エクセル　VBAでの質問です。

自分以外のファイルの値7を参照するエクセルを作っています。

sheet１のA1～B4に入力欄があり、
そこにフォルダ名、ファイル名、レンジを入力し、sheet１に表示しようと思っています。

しかし、A1～B4までの範囲を設定してしまうと、入力欄が上書きされてしまいます。
それを回避するためにオフセットさせようと思い、
自分で色々やってみたのですが、うまくいかないので教えてください。

要望としては、Sheet1B4にA1:F10と入力されてた場合、
Sheet1のC5からその範囲を表示させたいです。

以下が詳細です。
ーーーーー
Sheet1
(B1)フォルダ名入力欄　例　C:\test\test2
(B2)空白　「コマンドボタン１」あり
(B3)20110101.xls
(B4)B5:D8

--------
’コード
Private　Sub　Commandbutton1_Click()
Dim　hani　As　Variant
hani　＝　Range（"B4").Value
Range（hani）　＝　"='"　&　Range("B1").Value & "\[" & Range("B3").value & "]Sheet1'!" &　Range("B4").Value’↑改行なしです。
End Sub
--------
です。

よろしくお願いします。

回答No.3

keithin · ベストアンサー

参照式を入れる基点（左上セル）はC5セル固定でいいんですね。

基本形は
RANGE("C5:E8").Formula = "=パス[ブック名.xls]シート名!B5"
のようにします。
左側が「目的の範囲の縦横寸法に合致した，C5を基点とするセル範囲」であり，逆に右側は「目的（参照元）の範囲の左上のセル１個を相対参照で参照する式」にするという原則を憶えてください。
あとは実地に合わせてのバリエーションです。

dim hani as string
dim s as string
hani = range("B4").value
s = split(hani,":")(0)

range("C5").resize(range(hani).rows.count, range(hani).columns.count).formula _
= "='" & range("B1") & "\[" & range("B3") & "]Sheet1'!" & s

お礼

皆さんの回答全部問題なくてbaまよったのですが、
1番シンプルなこれにしました

回答No.2

Wendy02

アイデアとしては面白いけれども、マクロらしくないマクロです。
例えば、以下のようになります。

Private　Sub　Commandbutton1_Click()
　Dim hani As Variant
　Dim rng As Range
　Dim m As Long, n As Long
　Dim i As Long, j As Long
　hani = Range("B4").Value
　Set rng = Range(hani)
　m = rng.Rows.Count: n = rng.Columns.Count
　For i = 1 To m
　　For j = 1 To n
　　　Range("C5").Cells(i, j).Formula = "='" & Range("B1").Value & "\[" & Range("B3").Value & "]Sheet1'!" & Range(hani).Cells(i, j).Address(1, 1)
　　Next j
　Next i
End Sub

しかし、私なら、エラー処理を行うようにして、このように書くかもしれません。
ただ、ファイルを開かないで値を取るというのは、イレギュラーなマクロであることには違いありません。ブックをオーブンして、値を取るのが基本です。

お礼

回答ありがとうございます

今勉強中なので非常に参考になります

表示結果は問題ありませんでした

回答No.1

imogasi

シートの指定はどうなったのかな。指定しないのかな。
下記ではＳｈｅｅｔ１に仮定。
ーー
下記は質問者のやり方と違ったやり方である。
どちらが普通のやり方かよく判らない。こちらも慣れない者には難しいと思う。
しかし、よく初心者が、ブックをオープンしないでとか勝手なことを質問するのがあるが、そういうのに対しては、下記は応えてない。
ーー
C:\Documents and Settings\ＸＸＸＸ\My Documents
（コマンドボタン貼り付け位置）
01ＢＢＢ.xls　コピー元ブック名
A1:B5　　　　コピー元セル範囲
ーーー
コード
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rng As String
Set wb2 = ActiveWorkbook
rng = wb2.Worksheets("Sheet1").Range("b4")
Set wb1 = Workbooks.Open(Range("b1") & "\" & Range("b3"))
wb1.Worksheets("sheet1").Range(rng).Copy wb2.Worksheets("Sheet1").Range("A20")
wb2.Worksheets("Sheet1").Activate
Application.ScreenUpdating = True
End Sub
ーー
コピー元のブックのＳｈｅｅｔ１のrange（”Ａ１；Ｂ５”）をコピーして、今開いている（このコードを入れている）ブックの
Ｓｈｅｅｔ１のＡ２５セルから貼り付けている。
このＡ２５を右上隅のセルとして、任意に指定すれば、好みの位置に貼り付けられる。

お礼

初心者な質問ですみませんでしたσ(^_^;)

最終的な表示結果はもんだいありませんでした

回答ありがとうございます！
勉強させてもらいます