Using cells to specify range
 

Hi all,
I've been trying to use Cells to specify the range values over which to
search.
The range will vary each time so I wanted to use indexes for rows and
columns but I keep getting an error. The code is below.

Dim dd, ee, n, relst, relen,reld,recst,recen,recd as Integer
relst = Worksheets("Set Up").Range("C7").Value
relen = Worksheets("Set Up").Range("D7").Value
reld = relen - relst + 1
dd = 3 + reld - 1
recst = Worksheets("Set Up").Range("C10").Value
recen = Worksheets("Set Up").Range("D10").Value
recd=recen-recst+1
ee=2+recd-1

Worksheets("Harvest").Activate
For Each c In Worksheets("Harvest").Range(Cells(3, 2), Cells(dd,
ee)).Cells
If Application.IsNumber(c.Value) Then n = n + 1
Next c

I believe the error lies with the use of Cells within the RANGE statement
because I can specify the standard format (e.g., "A3:T19") and the statement
works.

I'd greatly appreciate any suggestions.
--
Gary

Using cells to specify range
 

Hi

First as you have activated "Harvest" sheet I wouldn't use the sheet
reference in
For each C In...

What is the value of the variables dd and ee when you get this error?

In your Dim statement only recd is declared as Integer all others are
declared as Variant. You have to declare variables one by one.

Hopes this helps.

---
Per

"fishguy" skrev i meddelelsen
...
Hi all,
I've been trying to use Cells to specify the range values over which to
search.
The range will vary each time so I wanted to use indexes for rows and
columns but I keep getting an error. The code is below.

Dim dd, ee, n, relst, relen,reld,recst,recen,recd as Integer
relst = Worksheets("Set Up").Range("C7").Value
relen = Worksheets("Set Up").Range("D7").Value
reld = relen - relst + 1
dd = 3 + reld - 1
recst = Worksheets("Set Up").Range("C10").Value
recen = Worksheets("Set Up").Range("D10").Value
recd=recen-recst+1
ee=2+recd-1

Worksheets("Harvest").Activate
For Each c In Worksheets("Harvest").Range(Cells(3, 2), Cells(dd,
ee)).Cells
If Application.IsNumber(c.Value) Then n = n + 1
Next c

I believe the error lies with the use of Cells within the RANGE statement
because I can specify the standard format (e.g., "A3:T19") and the
statement
works.

I'd greatly appreciate any suggestions.
--
Gary