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:J1 00"),
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" wrote in message
...
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("P rices").Range
("A2") .Value)


--
---
HTH

Bob

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



"Bob Phillips" wrote in message
...
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" wrote in message
...
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:J1 00"),
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