Hi All

Having a hard time with the following

Private Sub Add1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers")
Worksheets("Customers").Select

Tb1.Value = Application.CountIf(Range("J3:J600"), "35") <<<< Working ok

What I need is

Tb1.Value = Application.CountIf(Range("J3:J600") ' ws("Prices") Range ("A2")
so that if I change the price from "35" to 40 in ("A2")("Prices") it will
count the "40" in the "Customers" sheet.

Is this possible please - over 2 hours down the line and still no further
forward.
--
Many thanks

hazel

Hazel,

Try

Tb1 = Application.WorksheetFunction.CountIf(Range("J1:J100"),
Sheets("Prices").Range("A1").Value)

"Hazel" wrote:

> Hi All
>
> Having a hard time with the following
>
> Private Sub Add1_Click()
> Dim iRow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Customers")
> Worksheets("Customers").Select
>
> Tb1.Value = Application.CountIf(Range("J3:J600"), "35") <<<< Working ok
>
> What I need is
>
> Tb1.Value = Application.CountIf(Range("J3:J600") ' ws("Prices") Range ("A2")
> so that if I change the price from "35" to 40 in ("A2")("Prices") it will
> count the "40" in the "Customers" sheet.
>
> Is this possible please - over 2 hours down the line and still no further
> forward.
> --
> Many thanks
>
> hazel

Tb1.Value = Application.CountIf(Range("J3:J600"),ws("Prices").Range ("A2")
..Value)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" <(E-Mail Removed)> wrote in message
news:0CCB4194-CB53-4D4E-97BF-(E-Mail Removed)...
> Hi All
>
> Having a hard time with the following
>
> Private Sub Add1_Click()
> Dim iRow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Customers")
> Worksheets("Customers").Select
>
> Tb1.Value = Application.CountIf(Range("J3:J600"), "35") <<<< Working ok
>
> What I need is
>
> Tb1.Value = Application.CountIf(Range("J3:J600") ' ws("Prices") Range
> ("A2")
> so that if I change the price from "35" to 40 in ("A2")("Prices") it will
> count the "40" in the "Customers" sheet.
>
> Is this possible please - over 2 hours down the line and still no further
> forward.
> --
> Many thanks
>
> hazel

typo

Tb1.Value = Application.CountIf(Range("J3:J600"),Worksheets("Prices").Range
("A2") .Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tb1.Value = Application.CountIf(Range("J3:J600"),ws("Prices").Range ("A2")
> .Value)
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "Hazel" <(E-Mail Removed)> wrote in message
> news:0CCB4194-CB53-4D4E-97BF-(E-Mail Removed)...
>> Hi All
>>
>> Having a hard time with the following
>>
>> Private Sub Add1_Click()
>> Dim iRow As Long
>> Dim ws As Worksheet
>> Set ws = Worksheets("Customers")
>> Worksheets("Customers").Select
>>
>> Tb1.Value = Application.CountIf(Range("J3:J600"), "35") <<<< Working ok
>>
>> What I need is
>>
>> Tb1.Value = Application.CountIf(Range("J3:J600") ' ws("Prices") Range
>> ("A2")
>> so that if I change the price from "35" to 40 in ("A2")("Prices") it will
>> count the "40" in the "Customers" sheet.
>>
>> Is this possible please - over 2 hours down the line and still no further
>> forward.
>> --
>> Many thanks
>>
>> hazel
>
>

Hi Mike & Bob

As usual, I come back from Lunch and my problems are solved -- thanks to you
both. Going to try and get the label now to read the Prices ("A2") and then
the jobs done
--
Many thanks

hazel


"Mike H" wrote:

> Hazel,
>
> Try
>
> Tb1 = Application.WorksheetFunction.CountIf(Range("J1:J100"),
> Sheets("Prices").Range("A1").Value)
>
> "Hazel" wrote:
>
> > Hi All
> >
> > Having a hard time with the following
> >
> > Private Sub Add1_Click()
> > Dim iRow As Long
> > Dim ws As Worksheet
> > Set ws = Worksheets("Customers")
> > Worksheets("Customers").Select
> >
> > Tb1.Value = Application.CountIf(Range("J3:J600"), "35") <<<< Working ok
> >
> > What I need is
> >
> > Tb1.Value = Application.CountIf(Range("J3:J600") ' ws("Prices") Range ("A2")
> > so that if I change the price from "35" to 40 in ("A2")("Prices") it will
> > count the "40" in the "Customers" sheet.
> >
> > Is this possible please - over 2 hours down the line and still no further
> > forward.
> > --
> > Many thanks
> >
> > hazel