SOLVED

SOLVED - Check based on whatever cell the User-Defined Formula (Function) is in - Set Target

Iron Contributor

Greetings! Can y'all help me?

 

     I have a function that acts as a cell formula. What's happening is: the little grey numbers represent the days since the Soldier went without a duty (CQ or Staff), I have a sheet that acts as a database for 2021 (2022 currently displayed), and I want the first cell in range "E" (1/1/YEAR) to dynamically reference the database's number based on the Solder's tag (Tag_PersonPos_2) to continue values. Since I named the database literally just "2021", I have code using .Find to get the cell address I want to reference dynamically, but I don't know how to make it check based on whatever cell the User-Defined formula is in, as opposed to a defined range or selection. What do I declare Target as? Please see the code below (and pictures for a visual of what I said) and I'd much appreciate any help. Thank you!

 

Active Sheet:

Formua.png

 

Previous Year Database - Ref & Tag:

PreviousYearRef.png

 

Active Sheet Tag:

ActiveYearRef.png

 

Code:   

 

 

Public Function WS_MINUS_REF(ByVal Target As Range)

'Target = ???

WS_MINUS_REF = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & Target.Row & ":E" & Target.Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2).Offset(0, -1).Address

'REFER TO CELL FORMULA IS IN DYNAMICALLY???!!!
        
End Function

 

 

 

2 Replies
best response confirmed by Kendethar (Iron Contributor)
Solution

Update:

 

I figured it out. Working:

Working.png

Working1.png

Untitled2.png

 

To make the user-defined formula be dynamic to whatever cell the formula is in:

1) I needed to not declare a target, range, selection, etc. at all.

2) Define the function within "With Application.Caller   *CODE*   End With"

3) Make it behave as regular formulas would regarding calculation using "Application.Volatile"

So basically, instead of Target.Row do just .row, and the solution is no definition of Target and use .Caller.

 

Code:

(Of course, I also put in all the checks (IFs) specific to my need.)

 

 

Public Function PreviousYear_LastKnown_VALUE()

Dim FirstYear_Val As Integer: FirstYear_Val = Sheet4.Name
Application.Volatile 'This is to calculate this User-Defined formula automatically with the rest of the formulas
With Application.Caller 'This is to refer dynamically to the cell the formula is in (allow for ".row")

If Not Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 < FirstYear_Val Then 'Check if greater than or equal to first generated year

If Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker"). _
Range("D2").Value - 1 & "").Cells, Worksheets("Troop to Task - Tracker").Range("E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2) > 0 Then 'Check if person exists

PrevYear_Rng = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & .Row & ":E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2).Offset(-1, -1).Address

If ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range( _
PrevYear_Rng).Value = "Staff" Or ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range _
("D2").Value - 1 & "").Range(PrevYear_Rng).Value = "CQ" Then 'Check if duty is last day of previous year

PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
Else
PreviousYear_LastKnown_VALUE = _
ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range(PrevYear_Rng).Value + 1
End If
Else
PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
End If
Else
PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
End If
End With

'CODE WITHOUT THE IFS:
'WS_MINUS_REF = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & .Row & ":E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2).Offset(-1, -1).Value

End Function

 

 

 

(My completed code, to whoever it may help.)

 

 

Public Function PreviousYear_LastKnown_VALUE()

Dim FirstYear_Val As Integer: FirstYear_Val = Sheet4.Name
Application.Volatile 'This is to calculate this User-Defined formula automatically with the rest of the formulas
With Application.Caller 'This is to refer dynamically to the cell the formula is in (allow for ".row")
If Worksheets("Formula & Code Data").Range("I10").Value = "No" Then GoTo Calculate_NA 'Check if not applicable (for macro enhancement)
On Error GoTo Calculate_NA

If .Parent.Name = Sheet1.Name Then 'Check if formula's in active year
    If Not Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 < _
    FirstYear_Val Then 'Check if greater than or equal to first generated year
    If Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker"). _
    Range("D2").Value - 1 & "").Cells, Worksheets("Troop to Task - Tracker").Range("E" & .Row). _
    Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2) > 0 Then 'Check if person exists
    PrevYear_Rng = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
    Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & .Row & ":E" & .Row). _
    Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2, Lookat:=xlWhole).Offset(-1, -1).Address
    'Note: "Lookat:=xlWhole" is necessary to get an exact match (I.e. Tag_PersonPos2 not matched/found with Tag_PersonPos29)
    If ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range( _
    PrevYear_Rng).Value = "Staff" Or ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range _
    ("D2").Value - 1 & "").Range(PrevYear_Rng).Value = "CQ" Then 'Check if duty is last day of previous year
    PreviousYear_LastKnown_VALUE = 1
    Else
    PreviousYear_LastKnown_VALUE = _
    ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range(PrevYear_Rng).Value + 1
    End If
    Else: PreviousYear_LastKnown_VALUE = 1: End If
    Else: PreviousYear_LastKnown_VALUE = 1: End If
ElseIf .Parent.Name = Sheet4.Name Then 'Check if formula's in first generated year
    'MAYBE ADD CODE FOR INPORTED DATABASE HERE???!!!
    PreviousYear_LastKnown_VALUE = 1
Else 'Formula's in second/third/forth generated year
    Dim NonActiveYear_SheetRef As Integer: NonActiveYear_SheetRef = .Parent.Name
    If Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("" & NonActiveYear_SheetRef - 1 & "").Cells, _
    .Offset(1, ThisWorkbook.Sheets("" & NonActiveYear_SheetRef & "").Range("B1").Value).Formula2) > 0 Then 'Check if person exists
    PrevYear_Rng_Other = ThisWorkbook.Sheets("" & NonActiveYear_SheetRef - 1 & "").Cells.Find(What:=.Offset(1, _
    ThisWorkbook.Sheets("" & NonActiveYear_SheetRef & "").Range("B1").Value).Formula2, Lookat:=xlWhole).Offset(-1, -1).Address
    'Note: "Lookat:=xlWhole" is necessary to get an exact match (I.e. Tag_PersonPos2 not matched/found with Tag_PersonPos29)
    If ThisWorkbook.Sheets("" & NonActiveYear_SheetRef - 1 & "").Range( _
    PrevYear_Rng_Other).Value = "Staff" Or ThisWorkbook.Sheets("" & NonActiveYear_SheetRef - 1 & "") _
    .Range(PrevYear_Rng_Other).Value = "CQ" Then 'Check if duty is last day of previous year
    PreviousYear_LastKnown_VALUE = 1
    Else
    PreviousYear_LastKnown_VALUE = ThisWorkbook.Sheets("" & NonActiveYear_SheetRef - 1 & "").Range(PrevYear_Rng_Other).Value + 1
    End If
    Else: PreviousYear_LastKnown_VALUE = 1: End If
End If

'Error Handler:
Calculate_NA: If PreviousYear_LastKnown_VALUE = 0 Or _
PreviousYear_LastKnown_VALUE = "" Then
PreviousYear_LastKnown_VALUE = 1: End If: End With

End Function
1 best response

Accepted Solutions
best response confirmed by Kendethar (Iron Contributor)
Solution

Update:

 

I figured it out. Working:

Working.png

Working1.png

Untitled2.png

 

To make the user-defined formula be dynamic to whatever cell the formula is in:

1) I needed to not declare a target, range, selection, etc. at all.

2) Define the function within "With Application.Caller   *CODE*   End With"

3) Make it behave as regular formulas would regarding calculation using "Application.Volatile"

So basically, instead of Target.Row do just .row, and the solution is no definition of Target and use .Caller.

 

Code:

(Of course, I also put in all the checks (IFs) specific to my need.)

 

 

Public Function PreviousYear_LastKnown_VALUE()

Dim FirstYear_Val As Integer: FirstYear_Val = Sheet4.Name
Application.Volatile 'This is to calculate this User-Defined formula automatically with the rest of the formulas
With Application.Caller 'This is to refer dynamically to the cell the formula is in (allow for ".row")

If Not Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 < FirstYear_Val Then 'Check if greater than or equal to first generated year

If Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker"). _
Range("D2").Value - 1 & "").Cells, Worksheets("Troop to Task - Tracker").Range("E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2) > 0 Then 'Check if person exists

PrevYear_Rng = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & .Row & ":E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2).Offset(-1, -1).Address

If ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range( _
PrevYear_Rng).Value = "Staff" Or ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range _
("D2").Value - 1 & "").Range(PrevYear_Rng).Value = "CQ" Then 'Check if duty is last day of previous year

PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
Else
PreviousYear_LastKnown_VALUE = _
ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & "").Range(PrevYear_Rng).Value + 1
End If
Else
PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
End If
Else
PreviousYear_LastKnown_VALUE = "This'll be 1 by default" '1
End If
End With

'CODE WITHOUT THE IFS:
'WS_MINUS_REF = ThisWorkbook.Sheets("" & Worksheets("Troop to Task - Tracker").Range("D2").Value - 1 & ""). _
Cells.Find(What:=Worksheets("Troop to Task - Tracker").Range("E" & .Row & ":E" & .Row). _
Offset(0, Worksheets("Formula & Code Data").Range("C16").Value + 4).Formula2).Offset(-1, -1).Value

End Function

 

 

 

View solution in original post