Sep 20 2021 03:17 PM - edited Sep 20 2021 05:27 PM
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:
Previous Year Database - Ref & Tag:
Active Sheet Tag:
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
Sep 20 2021 05:25 PM - edited Sep 21 2021 11:22 AM
SolutionUpdate:
I figured it out. Working:
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
Sep 20 2021 09:31 PM - edited Sep 24 2021 11:23 PM
(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
Sep 20 2021 05:25 PM - edited Sep 21 2021 11:22 AM
SolutionUpdate:
I figured it out. Working:
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