Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement | Excel Programming |