CODEWITHSUNDEEP
excelvba
NormX
NormX

Reputation: 125

Set variable range in VBA

I have a function i am trying to create that basically clears the dropdown chose if dropdown in the previous cell is changed. I need to repeat this for many rows. currently it says my variable is not defined

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 17 To 1015
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("D" & i)) Is Nothing Then
    Range("E" & i).ClearContents
End If


End Sub

Upvotes: 1

Views: 108

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57673

This error means you need to declare your variable i and specify a type before you can use it. You do this by Dim i As Long.

Additionally you will need a Next i to tell where the loop ends.

Also test If Target.Cells.Count > 1 Then Exit Sub this only once (outside the loop).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    Dim i As Long
    For i = 17 To 1015
        If Not Intersect(Target, Range("D" & i)) Is Nothing Then
            Range("E" & i).ClearContents
        End If
    Next i
End Sub

Didn't test it but this should give the same result:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("D17:D1015")) Is Nothing Then
        Target.Offset(ColumnOffset:=1).ClearContents
    End If
End Sub

This should be faster because it doesn't need to loop.

Upvotes: 3

Nick
Nick

Reputation: 3845

Type the following just below Private Sub Worksheet_Change(ByVal Target As Range)

Dim i as long

You need to define what i is before you can use it.

Upvotes: 2

Related Questions