みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

以下が詳細です。
ーーーーー
Sheet1
(B1)フォルダ名入力欄 例 C:\test\test2
(B2)空白 「コマンドボタン1」あり
(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
--------
です。

よろしくお願いします。

投稿日時 - 2011-01-31 20:12:07

QNo.6488757

困ってます

質問者が選んだベストアンサー

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

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


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

投稿日時 - 2011-02-01 01:52:55

お礼

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

投稿日時 - 2011-02-01 10:08:40

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

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

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

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

投稿日時 - 2011-01-31 22:47:56

お礼

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

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

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

投稿日時 - 2011-02-01 09:22:15

ANo.1

シートの指定はどうなったのかな。指定しないのかな。
下記ではSheet1に仮定。
ーー
下記は質問者のやり方と違ったやり方である。
どちらが普通のやり方かよく判らない。こちらも慣れない者には難しいと思う。
しかし、よく初心者が、ブックをオープンしないでとか勝手なことを質問するのがあるが、そういうのに対しては、下記は応えてない。
ーー
C:\Documents and Settings\XXXX\My Documents
(コマンドボタン貼り付け位置)
01BBB.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
ーー
コピー元のブックのSheet1のrange(”A1;B5”)をコピーして、今開いている(このコードを入れている)ブックの
Sheet1のA25セルから貼り付けている。
このA25を右上隅のセルとして、任意に指定すれば、好みの位置に貼り付けられる。

投稿日時 - 2011-01-31 21:42:44

お礼

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

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

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

投稿日時 - 2011-02-01 09:15:39

あなたにオススメの質問