| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need VBA for a worksheet function
As a beginner I'm coming along nicely with my VBA project, but I'm
befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need VBA for a worksheet function
What is the rule for determining the Range each month?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message ups.com... As a beginner I'm coming along nicely with my VBA project, but I'm befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need VBA for a worksheet function
First, the way the thing works is that it retrieves the data from a
worksheet that another office worker compiles. When a new month happens they enter the new monthly values for the work days in the column A rows. My sheet compares them, and then copies and pastes those values into my column A. There's a blank row between months. So the rule for determining the starting row each month is: Range("A1").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(2, 0).Select Set StartCell = ActiveCell So that's my starting point for entering the formulas. Then I'll march across the row entering the formulas on the first of the month. During the month I'll auto fill them to thru the next day where there's data. Did I answer your question? Robert Bob Phillips wrote: What is the rule for determining the Range each month? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message ups.com... As a beginner I'm coming along nicely with my VBA project, but I'm befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need VBA for a worksheet function
I don't think so. As I read it, that tells me how to get to where to insert
it, but I was wondering how the A335,R316:R317,A316:A317 is determined. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message oups.com... First, the way the thing works is that it retrieves the data from a worksheet that another office worker compiles. When a new month happens they enter the new monthly values for the work days in the column A rows. My sheet compares them, and then copies and pastes those values into my column A. There's a blank row between months. So the rule for determining the starting row each month is: Range("A1").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(2, 0).Select Set StartCell = ActiveCell So that's my starting point for entering the formulas. Then I'll march across the row entering the formulas on the first of the month. During the month I'll auto fill them to thru the next day where there's data. Did I answer your question? Robert Bob Phillips wrote: What is the rule for determining the Range each month? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message ups.com... As a beginner I'm coming along nicely with my VBA project, but I'm befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need VBA for a worksheet function
The columns always stay the same. Column A in this case contains
dates. One row per day. Column R contains the current gross profit. The row numbers correspond to the current month. In this example, row 316 to 315 contains all of our work days for the month of Dec. So, after I paste the new monthly date range in column A, I use the ..End(xlUp) method to find the row number for the last row in the pasted range, which will be the maximum row for the formula. Thanks very much for your help with this Bob. Robert Bob Phillips wrote: I don't think so. As I read it, that tells me how to get to where to insert it, but I was wondering how the A335,R316:R317,A316:A317 is determined. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message oups.com... First, the way the thing works is that it retrieves the data from a worksheet that another office worker compiles. When a new month happens they enter the new monthly values for the work days in the column A rows. My sheet compares them, and then copies and pastes those values into my column A. There's a blank row between months. So the rule for determining the starting row each month is: Range("A1").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(2, 0).Select Set StartCell = ActiveCell So that's my starting point for entering the formulas. Then I'll march across the row entering the formulas on the first of the month. During the month I'll auto fill them to thru the next day where there's data. Did I answer your question? Robert Bob Phillips wrote: What is the rule for determining the Range each month? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message ups.com... As a beginner I'm coming along nicely with my VBA project, but I'm befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need VBA for a worksheet function
Robert,
I am sure I haven't got it (completely) right, but I think we need to try something so that you can tell me where we are going wrong. Dim iLastRow As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Cells(iLastRow + 2, "A").Formula = "=FORECAST(A335," & _ "$R$" & iLastRow - 1 & ":$R$" & iLastRow & _ ",$A$" & iLastRow - 1 & ":$A$" & iLastRow & ")" -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message oups.com... The columns always stay the same. Column A in this case contains dates. One row per day. Column R contains the current gross profit. The row numbers correspond to the current month. In this example, row 316 to 315 contains all of our work days for the month of Dec. So, after I paste the new monthly date range in column A, I use the .End(xlUp) method to find the row number for the last row in the pasted range, which will be the maximum row for the formula. Thanks very much for your help with this Bob. Robert Bob Phillips wrote: I don't think so. As I read it, that tells me how to get to where to insert it, but I was wondering how the A335,R316:R317,A316:A317 is determined. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message oups.com... First, the way the thing works is that it retrieves the data from a worksheet that another office worker compiles. When a new month happens they enter the new monthly values for the work days in the column A rows. My sheet compares them, and then copies and pastes those values into my column A. There's a blank row between months. So the rule for determining the starting row each month is: Range("A1").Select Range("A65536").End(xlUp).Select ActiveCell.Offset(2, 0).Select Set StartCell = ActiveCell So that's my starting point for entering the formulas. Then I'll march across the row entering the formulas on the first of the month. During the month I'll auto fill them to thru the next day where there's data. Did I answer your question? Robert Bob Phillips wrote: What is the rule for determining the Range each month? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "xjetjockey" wrote in message ups.com... As a beginner I'm coming along nicely with my VBA project, but I'm befuddled on how to write the code for the following function. I am attempting to write a procedure which will enter this (and other formulas) into selected cells at the first of each month. The formulas will be autofilled to the subsequent row on the next day. The problem is that the row numbers change each month. I can determine starting and ending row numbers with VBA The columns will always stay the same. How do I write a formula to reference the rowsI identified in VBA? How do I handle absolute and relative rows? This is one of the formulas: =FORECAST($A$335,$R$316:$R317,$A$316:$A317) Any help would be greatly appreciated. Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get ONLY new info from 1 Worksheet to another automatical | Excel Worksheet Functions | |||
| CELL Function and Worksheet Name in a Cell | Excel Worksheet Functions | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| Passing a WorkSheet from a Function??? | Excel Worksheet Functions | |||
| numerical integration | Excel Discussion (Misc queries) | |||
Hybrid Mode
