If value is in array then

Question

What I'm trying to achieve is a loop that will check if a cell (which will be two letters of the alphabet) is found in a array, if this is the case other stuff will happen.

So far my code looks like:

Sub Mortgagee()

    Dim Symbol As Variant
    Dim i As Long
    Symbol = Range("C1:C11").Value

    For i = LBound(Symbol, 1) To UBound(Symbol, 1)
        If Symbol.contains("A1") Then
            Range("G1").Copy
            Range("A1").Select
            ActiveSheet.Paste
        End If
    Next i

End Sub

What is your actual question? When happens when you run this code? And where did you defined "Codes" in "Ubound(Codes,1)"? — Julien Marrec
– Julien Marrec, Commented Jul 14, 2013 at 21:04
On a one-dimensional array just use Application.Match function e.g., If Not IsError(Application.Match(Symbol, Range("A1").Value, False)) Then ... — David Zemens
– David Zemens, Commented Jul 14, 2013 at 21:25
if you want to know if the array value contains-as opposed to equals-the value in A1 use Instr: If Instr(1, Symbol(i), Range("A1"), vbTextCompare) > 0 Then — JosieP
– JosieP, Commented Jul 15, 2013 at 8:11

rwisch45 · Accepted Answer · 2013-07-14 21:41:54Z

1

In your code above, Symbol is only taking the value of the first cell in the range - in this case, it is just taking the value of whatever is in cell C1.

I'm going to assume that you what you are trying to do is check if the value of a cell exists in an array - not if the cell itself is (which would mean you had an array of cell, or Range, objects).

Sub Mortgagee()
     Dim i as Long
     Dim arrSymbol(1 to 11) as String

     For i = 1 to UBound(arrSymbol)
          If arrSymbol(i) = "value to match" Then
               'Do work here
          End If
     Next i

End Sub

If you provide more information about the problem, specifically what value(s) you are checking for and also where Codes came from and what it is then I can help you out some more.

edited Jul 14, 2013 at 21:41

answered Jul 14, 2013 at 21:20

rwisch45

3,7022 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TinyTim Over a year ago

Codes was a typo, was meant to say symbol

Collectives™ on Stack Overflow

If value is in array then

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related