VBA for each文が上手くいかないです

Question

VBAで質問です
T列が終了の時、U列を解約の表示にしたいのですが、U列以外の列にも解約の表示がされてしまします。
VBAの書き方がどこが間違っているのかがわかりません。

Sub 終了を解約に変更()
Range("A3").AutoFilter 19, "終了"

With Range("A3").CurrentRegion.Offset(2, 0)
For Each R In .Resize(.Rows.Count - 2).SpecialCells(xlCellTypeVisible).Rows
R.Range("U1") = "解約"
Next R
End With

'絞り込みクリア
ActiveSheet.ShowAllData
End Sub

Answer 1

それ、セル側に関数を入れ込むだけじゃだめなんですか？
U列側に「解約」以外の文字列が入らずに「解約かどうかを表示するだけ」なら
U列のセルに
＝if(T1="終了","解約”,"")
と入れてそれを必要行数分コピー、あとはその列のロック設定をして(あるいは頻繁に入力する列のロックを解除して)シートのプロテクトを掛けておく

で、vba上でシート内のセルを改変したいときにプロテクトを解除する

ただ「別のセルの条件で解約と表示させるだけ」なら仰々しくvbaでプログラムを組むまでもない気がします。

Answer 2

Range("A3").AutoFilter 19, "終了"
AutoFilter.Range.Columns("U").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks) = "解約"
ShowAllData

Answer 3

提示のコードではU列以外に入力することはないので、別の部分に原因があると思われます。
シートモジュールのイベントで何かしていませんか。

下記のようにイベント抑制のコードを埋め込んだらどうなりますか。

Sub 終了を解約に変更()
    Application.EnableEvents = False 'イベント抑制
    
    Range("A3").AutoFilter 19, "終了"

    Dim r As Range
    With Range("A3").CurrentRegion.Offset(2, 0)
        For Each r In .Resize(.Rows.Count - 2).SpecialCells(xlCellTypeVisible).Rows
            r.Range("U1") = "解約"
            Debug.Print r.Address
        Next r
    End With

    '絞り込みクリア
    ActiveSheet.ShowAllData
    
    Application.EnableEvents = True
End Sub

---

以下、蛇足ですが、この場合、ループしなくても下記のようにすれば一気に入力できます。

Sub 終了を解約に変更()
    Application.EnableEvents = False 'イベント抑制
    
    Range("A3").AutoFilter 19, "終了"

    Dim r As Range
    With Range("A3").CurrentRegion.Offset(2, 0)
        .Resize(.Rows.Count - 2).Columns("U").SpecialCells(xlCellTypeVisible).Value = "解約"
    End With

    '絞り込みクリア
    ActiveSheet.ShowAllData
    Application.EnableEvents = True
End Sub

Comments

[goriwo]

抑制コードを打ち込んでも変わりませんでした、、、

特に自分ではイベント書いてないのですがちょっと調べてみます

Answer 4

Range("A3")が何なのかわからないので、とりあえず「T列が終了の時､U列を解約の表示にしたい」だけならこれでいいかなと。

Sub a()

Dim maxRow As Integer
maxRow = 100 '処理する最大行数

For i = 1 To maxRow
    If (IsError(Range("T" & i).Value) = False) Then
        If (Range("T" & i).Value = "終了") Then
            Range("U" & i).Value = "解約"
        End If
    End If
Next
End Sub

最大行数が何かで取得できるなら変数に入れても良いのかなと思います。

IsErrorのIF文を追記しました。
あくまでエラーを無視するだけなので、正しく処理するならエラーをcatchして処理する方がよいです。

Comments

[goriwo]

ありがとうございます
こういうやり方もあるのですね
勉強になります

正常に動いて助かったのですが、T列に中に#NUM！が合ってここで「型が一致しません」となってしまいます。
エラーをなおせばいい話なんですが、エラーをなおさず、このコードが上手くいく方法ってありますかね？

[mdj]

追記しました。これも「エラーを直さずに動かす」という点からだけのものなので、何にでもこれで対応できる、というわけでもありませんのでご留意ください。

[goriwo]

無事にエラーなく動きました
ありがとうございます

自分の説明の下手さを痛感しました、、、
助かりました！ありがとうございました！