How do I reference Current Cell when cell isn't the Activecell

  • Thread starter Thread starter VBA2VBZGuy
  • Start date Start date
V

VBA2VBZGuy

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
 
Good Day,

I am having a similar problem. I am new to vba and have been trying to teach myself vba through trial and error and forum posts, so thank you all!

I have share price data and have identified crashes. now i want to write a vba to calc how many days did it take till the share price recovered. I have this part working but at i used "activeCell" when i copy this function down the column i have to put the cursor and push enter on each cell before it calculates the correct value.

Any and all help would be appreciated.

Here is my function so far:

Function CalcDaysRecovery1() As String

cellA = ActiveCell.Offset(-1, -8)
'cellA is the share's price column and row -1 to get the prior days value as i was 'calculating a 1 day share price crash of at least 10% relative to an index

If ActiveCell.Offset(0, -1) < 0 Then


i = True

k = 1

While i
cellB = ActiveCell.Offset(k, -8)
If (cellB >= cellA) Then
CalcDaysRecovery1 = Str(k)

i = False
End If
k = k + 1
Wend
Else: CalcDaysRecovery1 = "No Crash"
End If
End Function

Thank you in advanced!

Kind Regards

Wayne
 
Back
Top