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
>

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:

> 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
> >
>
>
>

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))

--
HTH

Bob Phillips

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

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:

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

"Micos3" <[email protected]> wrote in message
news:[email protected]...
> Sorry, i only see your anwser now, it works.
> I just can't make it work to count the values between 2 cells.

What exactly does that statement mean?