Visual Basic

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?

Question not clear.

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

--
HTH

Bob Phillips

"Micos3" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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

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

--
HTH

Bob Phillips

"Micos3" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

I think you mean duplicates

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

--
HTH

Bob Phillips

"Micos3" <[email protected]> wrote in message
news:[email protected]...
> 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
>