Need VBA for a worksheet function

[xjetjockey]

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

 

[Bob Phillips]

What is the rule for determining the Range each month?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[xjetjockey]

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]

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]

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]

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]

No, but it's close enough for me to be able to fix it to make it work,
and you've essentially answered my question, for which I am much
obliged.

BTW, sometimes I outsource VBA coding for projects. Is this something
that you do? If so, please email me at (e-mail address removed).
Thanks again.

Robert