vba to check to see if a a cell equals a ranged list

[ahrion]

Hello, I'm new here but I have used this forum quite a bit.

I currently have module that calls to the below code any time a cell is is changed in a sheet. It takes a little bit of time to execute the code, a fraction of a second maybe, which is too much for what I'm doing. Is there any way to make this happen quicker and more elegantly? I essentially need to check if X cell is equal to Y ranged list, else = ""

Dim C As Range

    With ActiveWorkbook.Worksheets("Exception")
        For Each C In Range(ActiveWorkbook.Worksheets("Helper").Range("Q4"), ActiveWorkbook.Worksheets("Helper").Range("Q" & Rows.Count).End(xlUp))
            If C.Value <> "" And .Range("E26") = C.Value Then
                Exit For
            Else
                .Range("E36") = ""
            End If
        Next C
    End With

 

[ahrion]

I forgot to mention that X cell can = any of Y in the list of ranged cells Q4:Q21, as long as it's not ""

 

[GWteB]

Not sure what you're trying to achieve, but note the below comments in your code:

Dim C As Range

    With ActiveWorkbook.Worksheets("Exception")
        For Each C In Range(ActiveWorkbook.Worksheets("Helper").Range("Q4"), ActiveWorkbook.Worksheets("Helper").Range("Q" & Rows.Count).End(xlUp))
            If C.Value <> "" And .Range("E26") = C.Value Then
                
                'do nothing and skip any subsequent comparisons
                Exit For
            Else
                ' do this once ...
                .Range("E36") = ""
                
                ' ... more than once is useless, so skip any subsequent comparisons:
                Exit For
            End If
        Next C
    End With

 

[ahrion]

Not sure what you're trying to achieve, but note the below comments in your code:

Dim C As Range

    With ActiveWorkbook.Worksheets("Exception")
        For Each C In Range(ActiveWorkbook.Worksheets("Helper").Range("Q4"), ActiveWorkbook.Worksheets("Helper").Range("Q" & Rows.Count).End(xlUp))
            If C.Value <> "" And .Range("E26") = C.Value Then
               
                'do nothing and skip any subsequent comparisons
                Exit For
            Else
                ' do this once ...
                .Range("E36") = ""
               
                ' ... more than once is useless, so skip any subsequent comparisons:
                Exit For
            End If
        Next C
    End With

Thank you. I'm simple trying to see if E26 = any of the values in the Q4: range. If it does, do nothing, if it doesn't, then make E26 blank.

 

[Fluff]

How about

   Dim Ary As Variant, Res As Variant
   With ActiveWorkbook.Worksheets("Helper")
      Ary = .Range("Q4", .Range("Q" & Rows.Count).End(xlUp)).Value
   End With
   With ActiveWorkbook.Worksheets("main")
      Res = Application.Match(.Range("E26").Value, Ary, 0)
      If IsError(Res) Then .Range("E26").Value = ""
   End With

 

[ahrion]

How about

   Dim Ary As Variant, Res As Variant
   With ActiveWorkbook.Worksheets("Helper")
      Ary = .Range("Q4", .Range("Q" & Rows.Count).End(xlUp)).Value
   End With
   With ActiveWorkbook.Worksheets("main")
      Res = Application.Match(.Range("E26").Value, Ary, 0)
      If IsError(Res) Then .Range("E26").Value = ""
   End With

This seemed to be the most elegant solution. Worked well for some other stuff too and it make code execution seem quicker. Thank you.

I can do the code below instead, right?

Dim Ary, Res As Variant

 

[Fluff]

As both variables are variants then yes you can, but if you did

        Dim Ary, Res As Range

then only Res would be a range