Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Range | Excel Worksheet Functions | |||
Set a range from a variable location | Excel Discussion (Misc queries) | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) | |||
Select Using Range | Excel Discussion (Misc queries) | |||
Select a range | Excel Discussion (Misc queries) |