Greetings and Happy Holidays!

I am stuck on a cell referencing issue

I am looking to total 1 row in column T based on if a date
in column Q falls
within a 52 week range from a date in a cell. Then
looking to average some weekly
outstanding amounts from column T that fall within this 52
week range. Maybe someone knows of another way like an
indirect lookup method but I am using VBA.

HERE is my problem with my VBA CODE:

I want to create this into a function using the offset
method but I
don't know how to reference the "current" cell because its
not the "active cell". Since it's not the active cell, I
can't use "Activecell.Offset(-2,0).Value" in my function.
Here is the Pseudo code. Could someone help me with the
missing link?

Public Sub FiftyTwoWeekAvg()

Dim SettlementDate As Range
Dim sh As Worksheet
Dim SheetCount As Integer

' Set NewOutstanding to zero
NewOutstanding = 0

' Find out how many worksheets there are in this workbook
SheetCount = ThisWorkbook.Worksheets.count

' Need to replace following code with something like the
following
Set SettlementDate = Cells(CurrentCell).Offset(0,-2)

' Run through the sheets and grab the outstandings
For x = 1 To SheetCount
Set sh = Worksheets(x)
For i = 10 To 1000
If IsDate(Cells(i, 17)) Then
If (DateDiff("w", Cells(i, 17),
SettlementDate) <= 52) Then
NewOutstanding = NewOutstanding + Cells(i -
2, 20)
NumberofWeeks = NumberofWeeks + 1
End If
End If
Next i
Next x
' return the value for the FiftyTwoWeekAvg
FiftyTwoWeekAvg = NewOutstanding / NumberofWeeks
End Function

Can you help?

if you mean the currentcell is the cell containing the formula, then you can
use

Dim cell as Range
set cell = Application.Caller

--
Regards,
Tom Ogilvy

"VBA2VBZGuy" wrote in message
...
Greetings and Happy Holidays!

I am stuck on a cell referencing issue

I am looking to total 1 row in column T based on if a date
in column Q falls
within a 52 week range from a date in a cell. Then
looking to average some weekly
outstanding amounts from column T that fall within this 52
week range. Maybe someone knows of another way like an
indirect lookup method but I am using VBA.

HERE is my problem with my VBA CODE:

I want to create this into a function using the offset
method but I
don't know how to reference the "current" cell because its
not the "active cell". Since it's not the active cell, I
can't use "Activecell.Offset(-2,0).Value" in my function.
Here is the Pseudo code. Could someone help me with the
missing link?

Public Sub FiftyTwoWeekAvg()

Dim SettlementDate As Range
Dim sh As Worksheet
Dim SheetCount As Integer

' Set NewOutstanding to zero
NewOutstanding = 0

' Find out how many worksheets there are in this workbook
SheetCount = ThisWorkbook.Worksheets.count

' Need to replace following code with something like the
following
Set SettlementDate = Cells(CurrentCell).Offset(0,-2)

' Run through the sheets and grab the outstandings
For x = 1 To SheetCount
Set sh = Worksheets(x)
For i = 10 To 1000
If IsDate(Cells(i, 17)) Then
If (DateDiff("w", Cells(i, 17),
SettlementDate) <= 52) Then
NewOutstanding = NewOutstanding + Cells(i -
2, 20)
NumberofWeeks = NumberofWeeks + 1
End If
End If
Next i
Next x
' return the value for the FiftyTwoWeekAvg
FiftyTwoWeekAvg = NewOutstanding / NumberofWeeks
End Function

Can you help?

Tom,

Using the following, gives me 'Object Required' Error '424'.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!