Use a Variable to select a range
 

I know this has to be fairly simple, but I've searched all the topics
and can't determine how to do the following. I am trying to select a
range based on certain criteria. I am looping through the range using
a counter.


For Counter = 9 To LoopValue
Set curcell = Worksheets("Compiled
Totals").Cells(Counter, 4)

If curcell.Value = 0 Then
Sheets("Compiled Totals").Range(Cells(Counter, 1),
Cells(Counter, 15)).Select
Selection.Copy
'Other code to paste the range on another
spreadsheet
End If
Next Counter


I keep getting the 1004 error for the following line:
Sheets("Compiled Totals").Range(Cells(Counter, 1), Cells(Counter,
15)).Select

What am I missing?

Thanks. Connie

Use a Variable to select a range
 

Connie,

most likely the Cells(Counter, 1) must be replaced with
Sheets("Compiled Totals").Cells(Counter, 1)

Sheets("Compiled Totals").Range(Sheets("Compiled
Totals").Cells(Counter, 1), Cells(Sheets("Compiled Totals").Counter,
15)).Select

I keep getting the 1004 error for the following line:
Sheets("Compiled Totals").Range(Cells(Counter, 1), Cells(Counter,
15)).Select

What am I missing?

HTH
Kostis Vezerides

Use a Variable to select a range
 

That worked beautifully! Thanks. Now I have another question. I am
using the following code to loop through a range of cells and copy each
row to another sheet in the workbook based on a criteria. Each row of
data in the range of cells represents an employee record. If the
employee does not have a technician number (column 4 is "0000" or is
blank ""), then I want to copy the record to the "Upload Data Hourly"
sheet. Otherwise if there is a technician number for the employee, I
want to copy the record to the "Upload Data Tech" sheet. I am
essentially trying to split the original range of cells and create a
separate sheet for hourly workers and a separate sheet for technicians.
The following code works, however, since I am selecting the
appropriate sheet to copy the record to, the screen flickers back and
forth between the sheets. Is there a way to do this without having to
select the sheet. I've thought of using autofilter, however, I haven't
had much luck with it. When I specify the critieria, the autofilter
either identifies the correct range or includes one more or one less
record. My approach is to loop through the file to make sure each
employee goes in the proper place. Thanks for your help!

'Loop value is the row of last data in the original range of cells.
The data starts on row 9.
For Counter = 9 To LoopValue
Set sh = Worksheets("Compiled Totals")
sh.Select
Set Curcell = sh.Cells(Counter, 4)
sh.Range(sh.Cells(Counter, 1), sh.Cells(Counter, 15)).Select
Selection.Copy
If Curcell.Text = "0000" Or Curcell.Text = "" Then
Set sh = Worksheets("Upload Data Hourly")
HourlyCounter = HourlyCounter + 1
Else
Set sh = Worksheets("Upload Data Tech")
TechCounter = TechCounter + 1
End If
sh.Select
sh.Range("A2").Select
Do Until ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
sh.Range("A2").Select
Next Counter
MsgBox "You have successfully created the data files for technician
and non technician hourly employees. Following are the employee
counts:" & vbCrLf & vbCrLf & "Number of technician records: " &
TechCounter & vbCrLf & vbCrLf & " Number of non technician records: " &
HourlyCounter



vezerid wrote:
Connie,

most likely the Cells(Counter, 1) must be replaced with
Sheets("Compiled Totals").Cells(Counter, 1)

Sheets("Compiled Totals").Range(Sheets("Compiled
Totals").Cells(Counter, 1), Cells(Sheets("Compiled Totals").Counter,
15)).Select

I keep getting the 1004 error for the following line:
Sheets("Compiled Totals").Range(Cells(Counter, 1), Cells(Counter,
15)).Select

What am I missing?

HTH
Kostis Vezerides

Use a Variable to select a range
 

The flickering is because you are relying on copy/paste and plenty of
..select to do your job. You don't have to do this.

I have made some changes to your code to achieve what you want. The
main trick is that you can assign a range (e.g. A1:A15) to a variable
of Variant data type.

Dim v
v = Range("A1:A15")
Range("B1:B15") = v

This code snippet eliminates the need for most of your
select/copy/paste code. Also, as a general programming practice, beware
that object identifications are "expensive" and should be avoided
inside loops. With all this in mind, here is a modification of your
code snippet to do the same:

Set src = Sheets("Compiled Totals")
For Counter = 9 To LoopValue
Set Currcell = src.Cells(Counter, 4)
v = src.Range(src.Cells(Counter, 1), src.Cells(Counter, 15))
If Currcell.Text = "0000" Or Currcell.Text = "" Then
Set dest = Worksheets("Upload Data Hourly")
HourlyCounter = HourlyCounter + 1
Else
Set dest = Worksheets("Upload Data Tech")
TechCounter = TechCounter + 1
End If
rowNum = 1
While dest.Cells(rowNum, 1) < ""
rowNum = rowNum + 1
Wend
dest.Range(dest.Cells(rowNum, 1), dest.Cells(rowNum, 15)) = v
Next Counter
msgbox "blabla"

No select, no copy/paste, just variable assignments.

HTH
Kostis


Connie wrote:
That worked beautifully! Thanks. Now I have another question. I am
using the following code to loop through a range of cells and copy each
row to another sheet in the workbook based on a criteria. Each row of
data in the range of cells represents an employee record. If the
employee does not have a technician number (column 4 is "0000" or is
blank ""), then I want to copy the record to the "Upload Data Hourly"
sheet. Otherwise if there is a technician number for the employee, I
want to copy the record to the "Upload Data Tech" sheet. I am
essentially trying to split the original range of cells and create a
separate sheet for hourly workers and a separate sheet for technicians.
The following code works, however, since I am selecting the
appropriate sheet to copy the record to, the screen flickers back and
forth between the sheets. Is there a way to do this without having to
select the sheet. I've thought of using autofilter, however, I haven't
had much luck with it. When I specify the critieria, the autofilter
either identifies the correct range or includes one more or one less
record. My approach is to loop through the file to make sure each
employee goes in the proper place. Thanks for your help!

'Loop value is the row of last data in the original range of cells.
The data starts on row 9.
For Counter = 9 To LoopValue
Set sh = Worksheets("Compiled Totals")
sh.Select
Set Curcell = sh.Cells(Counter, 4)
sh.Range(sh.Cells(Counter, 1), sh.Cells(Counter, 15)).Select
Selection.Copy
If Curcell.Text = "0000" Or Curcell.Text = "" Then
Set sh = Worksheets("Upload Data Hourly")
HourlyCounter = HourlyCounter + 1
Else
Set sh = Worksheets("Upload Data Tech")
TechCounter = TechCounter + 1
End If
sh.Select
sh.Range("A2").Select
Do Until ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
sh.Range("A2").Select
Next Counter
MsgBox "You have successfully created the data files for technician
and non technician hourly employees. Following are the employee
counts:" & vbCrLf & vbCrLf & "Number of technician records: " &
TechCounter & vbCrLf & vbCrLf & " Number of non technician records: " &
HourlyCounter



vezerid wrote:
Connie,

most likely the Cells(Counter, 1) must be replaced with
Sheets("Compiled Totals").Cells(Counter, 1)

Sheets("Compiled Totals").Range(Sheets("Compiled
Totals").Cells(Counter, 1), Cells(Sheets("Compiled Totals").Counter,
15)).Select

I keep getting the 1004 error for the following line:
Sheets("Compiled Totals").Range(Cells(Counter, 1), Cells(Counter,
15)).Select

What am I missing?

HTH
Kostis Vezerides