Excel IF statement in macros

[DCD]

Hello all,

My Excel sheet is generating random numbers from 1 to 10 using the randbetween function. I am trying to write a macro to keep a count of how many of each number there are and display those numbers on the sheet as the random number is created.

To create each random number I have a button attached to a macro which just has the instruction calculate. This works fine.

After the calculate instruction I have added some code that I thought should keep a count and display each count on the sheet:

IF range("H14") = 1 then
range ("B2") = range ("B2") +1
END IF

and then another similar 9 of these if H14 has a value of 2 or 3 etc

(H14 is the cell that contains the random numbers and B2, C2 etc are where I want to display the count results).

When I activate the macro by clicking on my button the new random number is generated but I get counts increasing in other cells as well as the targeted one. ie if the random number is a 5, my count for the number 7 may increase by 1.

Questions
1. Is this the simplest way to keep a count of each random number?

2. What's wrong with the macro?

Any help appreciated,

DCD

[BrianB]

Try this :-

Sub test()
    Dim myrand As Integer
    myrand = Application.WorksheetFunction.randbetween(1, 9)
    'myrand = Int(Rnd * 9) + 1  ' VBA version
    c = myrand + 1
    ActiveSheet.Cells(2, c).Value = ActiveSheet.Cells(2, c).Value + 1
End Sub

[DCD]

Thanks for the reply Brian.

However I'm trying to teach some simple concepts to some grade 9 students and would like to keep the VBA stuff to a minimum.

I still don't understand why my original idea doesn't work. Have tried to adapt what you have said (.value stuff) and although it allows the code to execute it still has the same problem of adding to cells that are not referred to in my code.

EG if my code says

IF activesheet.cells(8,14).value = 1 then
acitivesheet.cells(2,1).value = activesheet.cells(2,1).value+1
end if

When executed other cells rather than cells(2,1) have 1 added to them.

Thanks again, I do appreciate your efforts,
DCD

[Ecniv]

Can you zip up the sheet n add to a post?

Vince

[Ecniv]

Or have a look at this.


Vince

[DCD]

Guys,

Really appreciate the help but as i said earlier i need to keep it simple for my students.

As suggested by Vince I have added it as a zip.

Thanks again,
DCD