エクセルVBA どうしても動きません。

質問

お世話になります。
なかなか上達しない超初心者です。
↓↓ですが、Activesheetにすると動くのに、シート名を元々存在するシート”実験"に指定すると動きません（データ内容はまったく同じで、A列に色んな名前が入っていて、重複しているとC列に重複と入力します）。
2日間試行錯誤したのですが、やっぱり動かず・・・
ちなみに、Activateにするとアクティブになるし、CurrentRegionも選択できるのに
なんでうごかないんでしょうか・・・
何卒ご指導くださいませ。よろしくお願いいたします！！！！！！

Sub Test2()

With Sheets("実験").Range("A1").CurrentRegion
Dim i As Long
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If WorksheetFunction.CountIf(Range("A:A"), Cells(i, 1)) > 1 Then
Cells(i, 3) = "重複"
End If

Next i
End With
End Sub

Sheets("実験")をActivesheetに変えると別のシートがしっかり動いてくれます・・・が、なんとしてでも”実験”シートに動いていただきたいです・・・。

回答No.6

HohoPapa · ベストアンサー

VBA、
とりわけ、With　と　CurrentRegion　
この２つの使い方を学習しているものと思います。

MINYA3481さんが既に理解している（だろう）とおり、
With Sheets("実験").Range("A1").CurrentRegion
End With
この2行に挟まれたコード群には、

MINYA3481さんの期待するセル範囲
（Sheets("実験")シートのA1セルから
　A1セルの次行のセル以下、空白が登場する直前まで）
が引き渡されます。

MINYA3481さんはこのセル範囲を処理の対象にしたいものと思います。
この引き渡されるセル範囲を受け取るには
.（ドット）で書き始める必要があります。
.（ドット）を省略した場合の挙動は他の方のコメントのとおりです。

都合、
With Sheets("実験").Range("A1").CurrentRegion
End With

この2行を生かすコードは次のような記述になります。

Sub Test4()

　With Sheets("実験").Range("A1").CurrentRegion

　　Dim i As Long

　　For i = 1 To .Rows.Count
　　　If WorksheetFunction.CountIf(.Range("A:A"), .Cells(i, 1)) > 1 Then
　　　　.Cells(i, 3) = "重複"
　　　End If
　　Next i

　End With
End Sub

'あるいは

Sub Test3()

　With Sheets("実験").Range("A1").CurrentRegion

　　Dim i As Long
　　Dim LastRow As Long

　　LastRow = .Rows.Count

　　For i = 1 To LastRow
　　　If WorksheetFunction.CountIf(.Range("A:A"), .Cells(i, 1)) > 1 Then
　　　　.Cells(i, 3) = "重複"
　　　End If
　　Next i

　End With

End Sub

お礼

みなさま、親切丁寧にご回答いただき、本当にありがとうございました！！！
会社を休んでいた間ずっと悩んでいたのに一気に解決！！
感謝でございます。
もっと勉強がんばります。

回答No.5

unokwave

Rows.CountのRowsも、LastRow = RangeのRangeも、Counifの中のRangeやCellsも、Then CellsのCellsも、上位指定の有無にで動作対象シートが左右されます。
WithでRangeオブジェクトを参照している時は、シート指定の代わりに.Rangeや.Cellsや.Rowsが使えます。
https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)#properties
VBA標準オブジェクトで.Parentプロパティがあるオブジェクトは、何かのオブジェクトの子オブジェクトですから、親を省略するとActiveなんとかが対象になります。

但し.Rows.Countを使った場合は、LastRow=の箇所は.Rows.Countを参照するだけで良い筈です。
CurrentRegionの結果の範囲になっていますから。

回答No.4

kkkkkm

No3の追記です。

Withで指定しないとなどでシートの指定がない場合
コードの存在する場所でセルの参照が変わります。
シートモジュールの場合はそのコードの存在するシートのセルを
標準モジュールの場合はアクティブなセルを
それぞれ指定したことになります。

回答No.3

kkkkkm

With Sheets("実験").Range("A1").CurrentRegion
を
With Sheets("実験")
.Range("A1").CurrentRegion
にして以下RangeとCellsの前にドット「.」を入れて
.Range(略)
.Cells(略)
にしたら動くのではないでしょうか。

回答No.2

kon555

LastRow = Range("A" & Rows.Count).End(xlUp).Row
を
LastRow = Sheets("実験").Range("A" & Rows.Count).End(xlUp).Row
にしましょう。
その他の部分も、セル指定する場合はその前にシート指定が必要です。

シート指定を省略した場合、自動的にアクティブなシートの指定と認識されてしまいます。Activesheetにすると動くのはそのためですね。
複数シートに跨るマクロを書くとよくやるミスです。覚えておきましょう。

回答No.1

f272

Range("A" & Rows.Count)
Range("A:A")
Cells(i, 1)
Cells(i, 3)
なんていうところでセルあるいはセル範囲を指定したいようだけど，どこのシートか明示的に書いていないので，VBAでは現在のシートのことだと解釈します。