Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-Each loop, I think.
Sorry this is so long, but I wanted to lay out what I have and what I
wanted... From a workbook (Staging List) we update each day, I want to create a list of records to inventory each day based on what rows they are in. This list is located in a worksheet named Official List. We have 40 rows in the warehouse, so I have split them up. Certain rows will be inventoried on Mon. Another set of rows on Tues, And so on each day thru Friday. So, each day Mon-Fri has a different set of rows to inventory. One of the column headings in the worksheet Official List is named Row. This column is used to show the row number that material is located. I've created a table in a worksheet named Cycle Count, which shows each day Monday thru Friday. And under each day is a list of all the rows to be counted on that day. I have name the ranges for each day. All the cells containing the rows to be counted on Monday are named CycleCount_Monday, for Tuesday, it's CycleCount_Tuesday, etc thru Friday. I have a formula that will identify the day of the week for the next workday. WEEKDAY(NextWorkdate). This formula is in a cell named DayofWeek. This will return a numeric value of the day of the week of the next workday (excludes weekends and holidays I have listed). If the next workday is Monday, the value in that cell will be 2, Tues=3, Wed=4, Thurs = 5 and Friday = 6. If, for example, today is Mon (Weekday value 2), and the Monday table of rows to be counted has R1, R2, and R3, then I want the macro to go through the worksheet, look in the column under Row, and for each record that has a row number that is in the Monday table (R1, R2 & R3), it should copy that record to another workbook (named for this example Cycle Count.xls. When the macro is done, the other workbook (Cycle Count.xls) will have a list of all the records that are in R1, R2 and R3. It would go something like this: range names are in ( ) If (DayofWeek) = 2 then look at (CycleCount_Monday) (not sure yet how I will include an If for each day of the week) Goto (Row) For each cell under Row that equals any of the rows in (CycleCount_Monday) Rows(ActiveCell.Row).Select Selection.Copy Windows("Cycle Count.xls").Open Goto (a named range where it will paste) I know I probably won't get an exact answer for what I need right away. But, maybe I can get it started with your help. Thanks, J.O. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For-Each loop, I think.
The best way to start is to record a macro of what you want to do. Then,
post the macro and ask for help with the variables. Regards, Alan "excelnut1954" wrote in message oups.com... Sorry this is so long, but I wanted to lay out what I have and what I wanted... From a workbook (Staging List) we update each day, I want to create a list of records to inventory each day based on what rows they are in. This list is located in a worksheet named Official List. We have 40 rows in the warehouse, so I have split them up. Certain rows will be inventoried on Mon. Another set of rows on Tues, And so on each day thru Friday. So, each day Mon-Fri has a different set of rows to inventory. One of the column headings in the worksheet Official List is named Row. This column is used to show the row number that material is located. I've created a table in a worksheet named Cycle Count, which shows each day Monday thru Friday. And under each day is a list of all the rows to be counted on that day. I have name the ranges for each day. All the cells containing the rows to be counted on Monday are named CycleCount_Monday, for Tuesday, it's CycleCount_Tuesday, etc thru Friday. I have a formula that will identify the day of the week for the next workday. WEEKDAY(NextWorkdate). This formula is in a cell named DayofWeek. This will return a numeric value of the day of the week of the next workday (excludes weekends and holidays I have listed). If the next workday is Monday, the value in that cell will be 2, Tues=3, Wed=4, Thurs = 5 and Friday = 6. If, for example, today is Mon (Weekday value 2), and the Monday table of rows to be counted has R1, R2, and R3, then I want the macro to go through the worksheet, look in the column under Row, and for each record that has a row number that is in the Monday table (R1, R2 & R3), it should copy that record to another workbook (named for this example Cycle Count.xls. When the macro is done, the other workbook (Cycle Count.xls) will have a list of all the records that are in R1, R2 and R3. It would go something like this: range names are in ( ) If (DayofWeek) = 2 then look at (CycleCount_Monday) (not sure yet how I will include an If for each day of the week) Goto (Row) For each cell under Row that equals any of the rows in (CycleCount_Monday) Rows(ActiveCell.Row).Select Selection.Copy Windows("Cycle Count.xls").Open Goto (a named range where it will paste) I know I probably won't get an exact answer for what I need right away. But, maybe I can get it started with your help. Thanks, J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |