Excel VBA how to search through 2d array

Question

Basically I am trying to Loop through all the values in the array and count the number of values that are greater than a value specified by the user by using Inputbox and also trying to use the IF statement to ensure a number between 1 and 100 is entered. After that is done I just want to simply display the results in a message box after.

Here is what I have so far:

Dim arr As Variant
arr = Range("A1:J10")

Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

Dim val As String
val = InputBox("Enter an integer value")
If val < 1 Or val > 100 Then
' tells the user to try again
    MsgBox "You did not enter a value from 1 to 100 , try again"
val = Inputbox("Enter an integer value")
Else

End If

Basically struggling with the if statement to verify the contents entered by the user and looping through the array.

I assume you want an or in the if statement as val cannot be both less than one and greater than 100. — Matt Cremeens
– Matt Cremeens, Commented Feb 6, 2017 at 20:55

Scott Holtzman · Accepted Answer · 2017-02-06 21:20:12Z

1

I suggest the following, which gets rid of loops and arrays all together by using the built-in CountIF function.

Do

    Dim val As Variant
    val = InputBox("Enter an integer value")

    If IsNumeric(val) And val > 1 And val < 100 Then

        Dim bPass As Boolean
        bPass = True

    End If

    If Not bPass Then MsgBox "You did not enter a value from 1 to 100 , try again"

Loop Until bPass

Dim lCountIf As Long

lCountIf = WorksheetFunction.CountIf(Range("A1:J10"), ">" & val)

MsgBox lCountIf & " values greater than " & val & "in Range."

answered Feb 6, 2017 at 21:20

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

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

Comments

user3598756 · Accepted Answer · 2017-02-06 21:30:45Z

1

I'd

use Application.InputBox() which lets you force a numeric input
avoid array and use WorksheetFunction.CountIf()

As follows:

Dim val As Integer

Do
    val = CInt(Application.InputBox(prompt:="Enter an integer value between 1 and 100", Type:=1))
Loop while val <1 And val > 100

MsgBox WorksheetFunction.CountIf(Range("A1:J10"), ">" & val)

answered Feb 6, 2017 at 21:30

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

2 Comments

Scott Holtzman Over a year ago

I was looking for that (Application.InputBox). My memory failed me on what the correct syntax was ... so I thought my memory just failed me on InputBox being able to force an integer :(

user3598756 Over a year ago

@ScottHoltzman, yeah it's something useful. I even prefer it to IsNumeric() since this latter isn't reliable (e.g.: ?IsNumeric("1..2") returns True...)

pokemon_Man · Accepted Answer · 2017-02-06 21:44:19Z

0

Dim val As String
val = InputBox("Enter an integer value")

Do While val < 1 Or val > 100
' tells the user to try again
    MsgBox "You did not enter a value from 1 to 100 , try again"
    val = InputBox("Enter an integer value")
Loop

'checks array if the number is greater then input number
Dim MyRange As Range
Dim cell As Range
Dim counter as integer
counter = 0
Set MyRange = Range("A1:J10")
For Each cell In MyRange
  If cell.Value > CInt(val) Then
    counter = counter + 1
    cell.Interior.Color = RGB(255, 0, 0) 'or cell.Interior.ColorIndex = 3
  End If
Next

msgbox "Total number greater then input number is: " & counter

edited Feb 6, 2017 at 21:44

answered Feb 6, 2017 at 21:00

pokemon_Man

9021 gold badge11 silver badges25 bronze badges

9 Comments

EricCarlson Over a year ago

would there be a way to verify val with only using an If/Else statement?

pokemon_Man Over a year ago

why do you insist on using if statement when a loop would be better in my opinion.

EricCarlson Over a year ago

Because I was going to use another loop which will count the values in the array greater than the one entered in val. was trying to avoid having a loop in a loop in this case

pokemon_Man Over a year ago

try the answer i posted to see if it does what you need.

EricCarlson Over a year ago

should i do Dim cell As Range to avoid another that is popping up?

|

Collectives™ on Stack Overflow

Excel VBA how to search through 2d array

3 Answers 3

Comments

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related