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

解決済みの質問

ExcelのVBで、Rangeの範囲に任意のセル値を使うには

VB初心者で行き詰っております。

前任者の作成したVBコードで、特定のセルの入力をテンキーで入力できるようにしています。
VBのコードについては、下記の通りとなっております。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim crng As Range
Dim ttarget As Range
Application.EnableEvents = False
Set ttarget = Application.Intersect(Target, Range("C6:AG35"))
If Not ttarget Is Nothing Then
ttarget = Application.VLookup(ttarget, Worksheets("入力").Range("A1:B10"), 2,False)
For Each crng In ttarget
If IsError(crng) Then
crng.Value = ""
End If
Next
End If
Application.EnableEvents = True
End Sub


ここで、シート内の範囲が変更となる(対象となる行が増減される)ことが発生します。

Set ttarget = Application.Intersect(Target, Range("C6:AG35"))

※この部分がRange("C6:AG37")や、Range("C6:AG33")などに変更となるということです。

その都度、VBの変更をかけると可能なのですが、より効率的にするため、入力という名前のシートに

ある空きセルをうまく活用したいと考えております。

Range("C6:AG35")の部分で、任意のセルを参照する変数を利用するには、
どのように記述したらよろしいでしょうか?


また、別法として、常に行数をカウントできる方法でも可能なのかもしれません。
最初の行に値するセルがC6から始まり、最終行よりも下は空白セルとなります。
とすれば、行数をカウントして変数に組み込むことも一つなのかもしれませんが、技術がないため、
思いつくだけにとどまっております。

アドバイスをいただければと思います。よろしくお願いします。

投稿日時 - 2009-11-25 11:50:20

QNo.5474562

すぐに回答ほしいです

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

Range("C6:AG35")

"C6:AG35"
は普通の文字列なので
A1セルに
C6:AG35
と書いたなら、マクロに
Range(Range("A1").Value)
とすれば良いかもしれない。
A1にC6(開始セル)
B1にAG35(終了セル)
なら、
Range(Range("A1").Value & ":" & Range("B1").Value)

投稿日時 - 2009-11-25 11:54:39

お礼

回答ありがとうございます.もう一つの回答とあわせてお礼させていただきます.

提示いただいたものを参考として,自分なりに考えてみました.
C6から値が入力されますが,隣のB6に入力(例:商品名)があることで,この範囲が適応されますので,
1.B1から最終行までをカウント
2.その行数分だけが,Range("C6:AG35")のAG**に相当する

ということで

Dim r As Long
Dim Area As String
r = Range("B1").End(xlDown).Row
Area = "C6:AG" & r
として,
Set ttarget = Application.Intersect(Target, Range(Area))

このように記述することで,別シートの空きセルに範囲指定を入力することなく,データと連動してRangeを変数とすることができました.

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

投稿日時 - 2009-11-26 08:48:02

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

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

回答(3)

ANo.3

こんにちは。

Worksheet_Change イベントの場合、その対象セル範囲を規定する必要がないのではないでしょうか。ある範囲を超えてはならないというなら別ですが、その範囲を全部指定してしまえばよいと思います。以下のマクロは、複数の貼り付けするとか、連結セルに貼り付けるとかの条件が付け加わったら、使えません。

'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ret As Variant
  
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If IsError(Target.Value) Then Exit Sub
  If Target.Row < 6 Or Not (Target.Column > 2 And Target.Column < 34) Then Exit Sub
  
  ret = Application.VLookup(Target, Worksheets("入力").Range("A1:B10"), 2, False)
  Application.EnableEvents = False
  If IsError(ret) Then
    Target.Value = ""
  Else
    Target.Value = ret
  End If
  Application.EnableEvents = True
End Sub

投稿日時 - 2009-11-25 12:34:29

お礼

回答ありがとうございます.
ある範囲内でのイベントのため,範囲指定をしています.

投稿日時 - 2009-11-26 08:26:39

ANo.2

> 最初の行に値するセルがC6から始まり、最終行よりも下は空白セルとなります。
最終行(のセル)は
Range("C65536").End(xlUp)
で見つかる。
行数が必ず2行以上あるのなら
Range("C6").End(xlDown)
でも可だが、1行しかないとき(C7以降が空白のとき)は正しく動作しない。

投稿日時 - 2009-11-25 11:57:24

あなたにオススメの質問