Hi All

I found the following in this forum and adapted it to my needs however I
have a problem - this is a membership annual payment subscription column and
there are
6 different values e.g £30, £15, £13, £10, £5, and Free and those values
could be in any cell in the column is it possible to add them up separately
and place the total values in different text boxes on the userform - Tb6 =
£30 Total Tb5 = £15 Total etc


Private Sub Add1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Members")
Set r = Range("P3:P401")
Count = 0
For Each rr In r
Count = Count + rr.Value
Next
Tb1.Value = Count


End Sub
--
Many Thanks

Sue

You can use a formula in a worksheet like:

=countif(P3401,30)
to count the cells that contain 30.

In your code you could use this to count the cells.

sometextbox.value _
= application.countif(worksheets("sheet9999").range("P3401"), 30)

You could multiply this result by 30 to get the total value

or

you could use:
=sumif(p3401,30)
in code:
sometextbox.value _
= application.sumif(worksheets("sheet9999").range("P3401"), 30)

Sue wrote:
>
> Hi All
>
> I found the following in this forum and adapted it to my needs however I
> have a problem - this is a membership annual payment subscription column and
> there are
> 6 different values e.g £30, £15, £13, £10, £5, and Free and those values
> could be in any cell in the column is it possible to add them up separately
> and place the total values in different text boxes on the userform - Tb6 =
> £30 Total Tb5 = £15 Total etc
>
> Private Sub Add1_Click()
> Dim ws As Worksheet
> Set ws = Worksheets("Members")
> Set r = Range("P3:P401")
> Count = 0
> For Each rr In r
> Count = Count + rr.Value
> Next
> Tb1.Value = Count
>
> End Sub
> --
> Many Thanks
>
> Sue

--

Dave Peterson

Hi Dave

Thanks for the all the help -- used the CountIf in code and it worked super
-- got rid of all those different colums for £30 & £15 etc -- all in one now
and can add and multiply values and manipulate them any way I want
--
Many Thanks

Sue


"Dave Peterson" wrote:

> You can use a formula in a worksheet like:
>
> =countif(P3401,30)
> to count the cells that contain 30.
>
> In your code you could use this to count the cells.
>
> sometextbox.value _
> = application.countif(worksheets("sheet9999").range("P3401"), 30)
>
> You could multiply this result by 30 to get the total value
>
> or
>
> you could use:
> =sumif(p3401,30)
> in code:
> sometextbox.value _
> = application.sumif(worksheets("sheet9999").range("P3401"), 30)
>
> Sue wrote:
> >
> > Hi All
> >
> > I found the following in this forum and adapted it to my needs however I
> > have a problem - this is a membership annual payment subscription column and
> > there are
> > 6 different values e.g £30, £15, £13, £10, £5, and Free and those values
> > could be in any cell in the column is it possible to add them up separately
> > and place the total values in different text boxes on the userform - Tb6 =
> > £30 Total Tb5 = £15 Total etc
> >
> > Private Sub Add1_Click()
> > Dim ws As Worksheet
> > Set ws = Worksheets("Members")
> > Set r = Range("P3:P401")
> > Count = 0
> > For Each rr In r
> > Count = Count + rr.Value
> > Next
> > Tb1.Value = Count
> >
> > End Sub
> > --
> > Many Thanks
> >
> > Sue
>
> --
>
> Dave Peterson
>