VBA - Check If A Number Is Not Found In An Array

[kelly mort]

I think I have some issues with my code which is not giving me the exact outcome I am looking for (independent of what I asked before).

This is how I am implementing the code in my workbook:

If Len(CmbAss1) Then

ConvPercent = Split(CmbAss1, "-")

ConvPer = Val(ConvPercent(0))

Select Case ConvPer

Case 20: strName() = [transpose(row(1:20))]

Case 30: strName() = [transpose(row(1:30))]

Case 40: strName() = [transpose(row(1:40))]

Case 50: strName() = [transpose(row(1:50))]

End Select



For i = 0 To 9

For Each cScore In sh.Range("I7:I" & lr).Offset(, i).Cells

If cScore <> "" Then

strFind = Val(cScore)

For j = LBound(strName, 1) To UBound(strName, 1)

If InStr(strName(j), strFind) = 0 Then

MsgBox "Sorry, " & cScore & ", is outside your selection", vbExclamation, ""

CmbAss1 = ""

Exit Sub

End If

Next j

End If

Next cScore

Next i

End If

Inside the CmbAss1 combobox, I will be making a selection which could be any of the 4:

20-80

30-70

40-60

50-50



Then I had the split function to split and take the first part as shown with my case statement above.

I will be looping through 10 columns. And in each column, I wanna check if there is a number which does not fall with the ConvPer value.



This part of the code:

For j = LBound(strName, 1) To UBound(strName, 1)

If InStr(strName(j), strFind) = 0 Then

MsgBox "Sorry, " & cScore & ", is outside your selection", vbExclamation, ""

CmbAss1 = ""

Exit Sub

End If

Next j

Is something I found on the web – I don’t really understand it and I am thinking I failed to tweak it to my needs. Is there something I am not able to spot?



Thanks in advance.

 

[NdNoviceHlp]

For j = LBound(strName) To UBound(strName)

Maybe your array is only 1 dimension? HTH. Dave

 

[Rick Rothstein]

For j = LBound(strName) To UBound(strName)

Maybe your array is only 1 dimension? HTH. Dave

The optional 2nd argument to LBound and UBound when it is a 1 works for one-dimensional arrays also.

 

[Marc L]

Hi,​

​

as this post was removed of your previous thread so I hope this time it could help you,​

just a little demonstration to check if a number is outside a range :​

Sub Demo1()
    Const C = "20-80", N = 8
    S = Split(C, "-")
    If IsError(Application.Match(N, Evaluate("COLUMN(" & Cells(S(0)).Resize(, S(1) - S(0) + 1).Address & ")"), 0)) Then _
        MsgBox N & " is outside the range " & C
End Sub

Do you like it ? So thanks to click on the bottom right ?Like icon ! ​

 

[NdNoviceHlp]

Thanks Rick for the learning. Kelly maybe this part is wrong...

strFind = Cstr(cScore)

The Instr function requires strings to be evaluated. HTH. Dave

 

[kelly mort]

Hi,​

​

as this post was removed of your previous thread so I hope this time it could help you,​

just a little demonstration to check if a number is outside a range :​

Sub Demo1()
    Const C = "20-80", N = 8
    S = Split(C, "-")
    If IsError(Application.Match(N, Evaluate("COLUMN(" & Cells(S(0)).Resize(, S(1) - S(0) + 1).Address & ")"), 0)) Then _
        MsgBox N & " is outside the range " & C
End Sub

Do you like it ? So thanks to click on the bottom right ?Like icon ! ​

cool. but the number 8 should not be outside the range 20-80. the 20-80 embraces numbers from 1 to 20.

thanks again for your time

 

[kelly mort]

Thanks Rick for the learning. Kelly maybe this part is wrong...

strFind = Cstr(cScore)

The Instr function requires strings to be evaluated. HTH. Dave

Oh okay - thanks, Dave.

I changed the

 strFind = Val(cScore)

to

 strFind = Cstr(cScore)

but I am still not getting it to work.

if i cannot use the Instr function for the evaluation of numbers, which function should i use in place of it?

Regards, Kelly.

 

[NdNoviceHlp]

Kelly it's kind of difficult to tell what range you're evaluating. It seems like U want to evaluate column "I" rows 7 to lastrow to column "I" plus 9 columns to lastrow. Anyways, that's what this does. For this trial, I just put the string from the combobox in A1 for testing. HTH. Dave

Sub test()
Dim strname() As Variant
Dim cscore As Range, lr As Integer, strFind As String
Dim sh As Worksheet, flag As Boolean
Set sh = ActiveSheet

If Len([sheet1!A1]) Then

ConvPercent = Split([sheet1!A1], "-")

ConvPer = Val(ConvPercent(0))

Select Case ConvPer
Case 20: strname() = [transpose(row(1:20))]

Case 30: strname() = [transpose(row(1:30))]

Case 40: strname() = [transpose(row(1:40))]

Case 50: strname() = [transpose(row(1:50))]

End Select


With sh
lr = .Range("I" & .Rows.Count).End(xlUp).Row
End With

For i = 9 To 18
For Each cscore In sh.Range(sh.Cells(7, i), sh.Cells(lr, i))
If cscore.Value <> "" Then
strFind = CStr(cscore)
flag = False

For j = LBound(strname) To UBound(strname)
If InStr(strname(j), strFind) Then
flag = True
End If
Next j

If Not flag Then
MsgBox "Sorry, " & cscore & ", is outside your selection", vbExclamation, ""
CmbAss1 = ""
End If
End If
Next cscore
Next i
End If
End Sub