ＶＢＡ(エクセル）でのＣＯＵＮＴＡについて

質問

エクセルのＳｈｅｅｔ１のＢ列にＳｈｅｅｔ２の内容をコピーして、(ここまではできました）
Ｓｈｅｅｔ１のＢ列に入ってきたデータの横（Ａ列に）連番を振りたいと思っています。
そのため、以下のように作ってみたのですが、
Ａ列に表示される連番が現在のＢ列の最後の数“５４”をＡ列全て(Ｂ列にデータがあるところ）に表示してしまいます。
どの部分が悪いのかさっぱりわからず、どのように修正すべきかもわからず・・・困ってしまっています。
よろしくお願いします。

Dim i As Range
Dim mycount As Range

Set mycount = Application.Intersect(Target, Me.Range("b:b"))

If mycount Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
For Each i In mycount
If IsEmpty(i.Value) Then
i.Offset(0, -1).ClearContents
Else
i.Offset(0, -1).Value = Application.WorksheetFunction.CountA(Range("b2:b200"))

End If
Next i
Application.EnableEvents = True

End Sub

回答No.2

KenKen_SP · ベストアンサー

#1の方の方法でも大丈夫ですが、変数でカウントした方が高速です。

'連番カウンタ初期値
lngCnt = 1
For Each i In mycount
　　If IsEmpty(i.Value) Then
　　　　i.Offset(0, -1).ClearContents
　　Else
　　　　'カウンタ値書込み
　　　　i.Offset(0, -1).Value = lngCnt
　　　　'カウントアップ
　　　　lngCnt = lngCnt + 1
　　End If
Next i

以下は、余談ですが。。

＞　Set mycount = Application.Intersect(Target, Me.Range("b:b"))

これは Target が B列 にあるか調べる方法ですね。もし、Target が B列にある場合、オブジェクト変数 mycount には B:B 、つまり B列全体がセットされます。これを For Each i in mycount ～ next i でセルを一個一個チェックさせると65536回ループが発生します。例えば、データが1件でも65536回チェックすることになります。

Set myCount = Application.Intersect(Target, Me.Range("b:b"))
If myCount Is Nothing Then
　　Exit Sub
End if
'再定義
Set myCount = Range(Range("B1"),Range("B1").End(xlDown))

とした方が良いかと思います。

また、Application.EnableEvents を使用する場合、例外処理を必ず書きましょう。
例えば、

　　On Error Goto ErrorHandler
　　Application.EnableEvents = False

　　（処理）

ExitHandler:
　　Exit Sub

ErrorHandler:
　　Application.EnableEvents = True
　　Resume ExitHandler

End Sub

のようにエラーが発生したら、ラベルErrorHandler にジャンプして EnableEventsを元に戻して終了させます。

お礼

とても詳しい解説をありがとうございます。
ＶＢＡはまったくの初心者で、本当にお恥ずかしい限りです。
教えていただいたコードでもう一度作ってみます。
ありがとうございました。

回答No.1

noname#29107

i.Offset(0, -1).Value = Application.WorksheetFunction.CountA(Range("b2:b200"))
を
i.Offset(0, -1).Value = Application.WorksheetFunction.CountA(Range("b2:b" & i.row ))
に変更したらどうでしょうか？

お礼

ありがとうございました。
ようやく完成することができました。