Visual Basic

M

Micos3

Hi
I've putted a question here yesterday, where i used this formula
=countif(Table;">"&Cell). This formula works, but the table in question is
builted with VB, that i'm not a expert, and so it does not appears the value
that it should.
My guess is because the values are in VB, so how can i read this value for
the formula works?
 
B

Bob Phillips

Question not clear.

Post the code that you build the table with and the full formula.
 
M

Micos3

Yu're right, i've not putted the question well. The table that it starts in
B11:K40 we fill with data, but then with VB it grabbs that values and uses it
to other things u can see in the program that i send.
What i want is to use the values in table B11:K40 and count with a
=countif(B11:K40;">"&B42) the numbers that are bigger than B42.
this formula works in a table alone but in the sheet i have that uses this
program below does not appear the value, so i guess is because of VB.
Is it?

The program:
Sub Frequência_1()
'
'
Application.ScreenUpdating = False
For i = 1 To 199
Valor(i) = 0
Next i

Range("B11").Select
K = 1
For i = 1 To 10
For j = 1 To 30
If ActiveCell.Value <> "" Then
Valor(K) = ActiveCell.Value
K = K + 1
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-30, 1).Select
Next i

Range("I72").Select
For i = 1 To 15
Cont = 0
Min = Selection
ActiveCell.Offset(0, 2).Select
Max = Selection
For j = 1 To K
If (Valor(j) >= Min) And (Valor(j) < Max) Then Cont = Cont + 1
Next j
ActiveCell.Offset(0, -6).Select
ActiveCell.Value = Cont
ActiveCell.Offset(1, 4).Select
Next i

Cr_1 = 0
Cr_2 = 0
Cr_3 = 0

Range("F42").Select
Média = ActiveCell.Value
Range("F46").Select
Desv = ActiveCell.Value
For i = 1 To K
If Valor(i) > (Média - Desv) And Valor(i) < (Média + Desv) Then Cr_1 =
Cr_1 + 1
If Valor(i) > (Média - 2 * Desv) And Valor(i) < (Média + 2 * Desv) Then
Cr_2 = Cr_2 + 1
If Valor(i) > (Média - 3 * Desv) And Valor(i) < (Média + 3 * Desv) Then
Cr_3 = Cr_3 + 1
Next i
Range("D90").Select
ActiveCell.Value = Cr_1
Range("D91").Select
ActiveCell.Value = Cr_2
Range("D92").Select
ActiveCell.Value = Cr_3




For i = 1 To 199
Valor(i) = 0
Next i

Range("M11").Select
K = 1
For j = 1 To 30
If ActiveCell.Value <> "" Then
Valor(K) = ActiveCell.Value
K = K + 1
End If
ActiveCell.Offset(1, 0).Select
Next j

Range("P72").Select
For i = 1 To 15
Cont = 0
Min = Selection
ActiveCell.Offset(0, 1).Select
Max = Selection
For j = 1 To K
If (Valor(j) >= Min) And (Valor(j) < Max) Then Cont = Cont + 1
Next j
ActiveCell.Offset(0, -3).Select
ActiveCell.Value = Cont
ActiveCell.Offset(1, 2).Select
Next i

Cr_1 = 0
Cr_2 = 0
Cr_3 = 0

Range("J42").Select
Média = ActiveCell.Value
Range("J46").Select
Desv = ActiveCell.Value
For i = 1 To K
If Valor(i) > (Média - Desv) And Valor(i) < (Média + Desv) Then Cr_1 =
Cr_1 + 1
If Valor(i) > (Média - 2 * Desv) And Valor(i) < (Média + 2 * Desv) Then
Cr_2 = Cr_2 + 1
If Valor(i) > (Média - 3 * Desv) And Valor(i) < (Média + 3 * Desv) Then
Cr_3 = Cr_3 + 1
Next i
Range("J90").Select
ActiveCell.Value = Cr_1
Range("J91").Select
ActiveCell.Value = Cr_2
Range("J92").Select
ActiveCell.Value = Cr_3

End Sub
 
B

Bob Phillips

I can't get it to run, I keep running into logic errors, mainly trying to
reference out of bounds in valor array.
 
M

Micos3

i don't know what is the problem on the VB and i think it could be managed in
other way,do u know any command that gives the value in a cell that has
values that aren't readed by the excell?
I once had a similar problem, cos excell didn't read the values of the cells
but a command that i don't remember, made excell to assume the values and so
i did the formula.
Do you know any command that does this?

Thanks
 
B

Bob Phillips

I think you mean duplicates

If Application.Countif(Range("B11:K40"),Activecell.Value) > 1 Then
'duplicates
 
M

Micos3

I'm a VB noob :(
how do i apply this lines in the program in excel?
Isn't there another simpler way? a funcion or something?
If u could tell how to do it i appriciate cos my knowledge of VB sucks.
Sorry to reply only now but yesterday i didn't saw your awnser.

Thanks again

"Bob Phillips" escreveu:
 
B

Bob Phillips

I'm struggling here because I can't get your code to run.and I can't work
out how B11:K40 are being populated.

It seems as if B11:K40 are text, but B42 is a number, so how about using
this formula instead

=SUMPRODUCT(--(B11:K40>B1))
 
M

Micos3

Sorry, i only see your anwser now, it works.
I just can't make it work to count the values between 2 cells.

Thanks

"Bob Phillips" escreveu:
 
B

Bob Phillips

Micos3 said:
Sorry, i only see your anwser now, it works.
I just can't make it work to count the values between 2 cells.
Click to expand...

What exactly does that statement mean?
 
Top