Assist in debugging vba module

Question

I was wondering if someone could assist me with debugging the following module, I believe my only issue lies in the last (if) statement in order for cells to be filled in with a color. The general module creates a cell range of randomized numbers that should be filled in with the colour red if an input of a certain value is entered in the input box. Thanks!

Option Explicit

Sub test()

Dim i As Integer
Dim j As Integer
Dim user As Integer
Dim cell As Range
Dim random_number As Integer
Dim itm As Integer


For Each cell In Range("A1:J10")

random_number = Int(100 * Rnd) + 1
cell.Value = random_number

Next

Dim mycount As Integer
 
Dim myarray(1 To 10, 1 To 10)

For i = 1 To 10
    For j = 1 To 10
         myarray(i, j) = Cells(i, j).Value
Next
Next

user = CInt(InputBox("enter a number betweeen 1 and 100"))
If user < 1 Or user > 100 Then
    msgbox ("Error, wrong value entered")
    Exit Sub
End If


For i = 1 To 10
    For j = 1 To 10
        If myarray(i, j) > user Then
           Range(Cells(i, j)).Interior.Color = RGB(255, 0, 0)


    itm = itm + 1
        End If

Next
Next


      
msgbox itm

     

End Sub

Ah thank you. I guess the range doesn't need to be defined afterwords. — JaySmith
– JaySmith, Commented Jun 10, 2017 at 3:00

A.S.H · Accepted Answer · 2017-06-10 01:51:40Z

1

See @Gary'sStudent comment to debug your code.

If you are willing to learn using the amazing power of arrays, ranges and the Excel/VBA duo, which is a must if you are going to work with this technology in the long term, try rewriting your code this way:

Sub test()
  Dim user As Long
  With Worksheets("Sheet1").Range("A1:J10")
    ' Generate random numbers using formula then fix values
    .Formula = "=RANDBETWEEN(1, 100)"
    .value = .value

    ' Enter a number
    user = Application.InputBox("enter a number betweeen 1 and 100", Type:=1) ' <-- Enter a number
    If user < 1 Or user > 100 Then
      MsgBox ("Error, wrong value entered")
      Exit Sub
    End If

    ' Higlight the value using conditional formatting.
    .FormatConditions.Delete
    .FormatConditions.Add(xlCellValue, xlGreater, user).Interior.Color = RGB(255, 0, 0)

   ' Count of items that match the value
    MsgBox Application.CountIf(.Cells, ">" & user)
 End With
End Sub

edited Jun 10, 2017 at 1:51

answered Jun 10, 2017 at 1:30

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

8 Comments

user4039065 Over a year ago

Ha! I had been thinking along these exact same lines.

user4039065 Over a year ago

(... but the user may get confused between .Name and .CodeName if not previously exposed to the use of .CodeName)

user4039065 Over a year ago

Should it be Application.CountIf(.Cells, ">" & user) ?

A.S.H Over a year ago

@Jeeped yes, I changed it to make it easier at this stage. Main thing was to put some dot . before the range ;)

A.S.H Over a year ago

@jeeped yes and also xlGreater.

|

Collectives™ on Stack Overflow

Assist in debugging vba module

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related