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

解決済みの質問

ExcelVBA コピー範囲を変数にしたい

いつもお世話になっております。
またVBAでお知恵をください。
SHEET1のある範囲(フラグがたっている)をSHEET2にコピーしたいのですが
フラグが存在しない場合もあるために悩んでいます。

途中まで自分でやってみたのですがエラーになりました。
Dim AA As Range
For Each AA In Range("F4:AL4") ←ここの範囲にフラグがあります
AA.Select
With ActiveCell
If .Value = "1A" Then Selection.Offset(7, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("SHEET2").Select
Range("F15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("sheet1").Select
End With
Next AA
フラグ列を探し、1Aがあったらその7つ下のセルからラストのセルまでの
値をSHEET2のF15にコピーしたいのです。
フラグは1A、1B、2A、2Bと変化し、
貼り付け場所はそれに応じて1A→F15、1B→G15、2A→I15
となります。(貼り付け先は固定)
ただし、フラグが存在しない場合もあり、その場合はSKIPです。
(1BがなければG15には何も貼り付けない)
フラグは10Bまで合計20個あるため、できれば変数にしたいのですが
どのように記述すればいいのかわかりません。

貼り付け先はF、G、I、J、L、M、O、P、と変化します。
(AとBでセットで間に1列あきます。が、Aだけの場合もある)
これも変数にすればよいのでしょうが、15行目、というのを
記述する方法と、存在しない場合に詰めてしまわないようにする
方法がわかりません。。。。
お知恵を貸してください。
よろしくお願いいたします。

投稿日時 - 2008-11-10 14:35:48

QNo.4467627

困ってます

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

せっかくExcelを使うんですから、フラグの値と貼り付けセル位置の関係をリストにして別シートに持ちましょう。
そのリストからVlookupでフラグの値に一致するセル位置を取得します。
例えばSheet3のA列にフラグの値、B列に選択セル位置を入れます。

1A F15
1B G15
2A I15
2B J15
: :

この状態で、元のマクロを次のように変えます(エラー処理等は手抜きです)。

Dim AA As Range
Dim sString As String '<--- セル位置用
For Each AA In Range("F4:AL4")
AA.Select
With ActiveCell

On Error Resume Next '<--- リスト不一致対策
sString = "" '<--- 編集を初期化
sString = WorksheetFunction.VLookup(.Value, Sheets("Sheet3").Range("A:B"), 2, True) '<--- リストから貼り付け位置取得
If sString <> "" Then '<--- 貼り付け位置が取得できた場合
 Selection.Offset(7, 0).Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Copy
 Sheets("SHEET2").Select
 Range(sString).Select '<--- 取得した貼り付け位置をSelect
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Sheets("sheet1").Select
End With
Next AA

別シートにリスト化する事でメンテナンスも容易になります。

投稿日時 - 2008-11-10 15:21:37

ANo.1

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

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

回答(2)

ANo.2

ちょっとコメント部を訂正

誤:sString = "" '<--- 編集を初期化
正:sString = "" '<--- 変数を初期化

投稿日時 - 2008-11-10 15:24:22

お礼

適切なアドバイスありがとうございます!
シート上に定義してLookupで読む方法があるのは
初めて知りました。
(毎回初めて知ることばかりなのですが・・・汗)
この方法で完全に解決しました。
本当に助かりました・・・。(涙)
ありがとうございました。。。

投稿日時 - 2008-11-10 15:33:59

あなたにオススメの質問