英文:
Excel Formula or VBA to find next cell in a row with a wildcard value AFTER date column
问题
我正在尝试创建一个显示学生下一个计划事件的列。我有一些可行的代码，但我需要为每一行单独复制粘贴并在添加或删除学生时进行更新。如果我有一个公式或代码可以覆盖整行而不需要不断更新，那将是巨大的帮助。
这是我们追踪器的简化版本。出于保密原因，我不得不删除一些信息。我希望B4:B6列显示在B3列跟踪的当前日期之后的下一个事件。
英文:
I am trying to make a column that shows a student's next planned event. I have some code that works but I would have to copy paste it for every single line offset and update it every time a student was added or removed. If I had a formula or code that would cover the row that wouldn't need constant updating, that'd be a huge help.
Here is a simplified version of our tracker. I had to erase info for confidentiality reasons. I want B4:B6 column to display the next event after the current date tracked by column in B3.
答案1
得分: 1
这是公式：
=IFERROR(INDEX(4:4,1,MIN(IF("&"&D4:K4<>"",IF(D$2:K$2>TODAY(),COLUMN(D4:K4),999999),999998))),"")
请将其键入C4并按Ctrl+Shift+Enter，因为这是一个数组公式。
根据需要修改以获取单元格值的正确部分。
英文:
Here is the formula:
=IFERROR(INDEX(4:4,1,MIN(IF(""&D4:K4<>"",IF(D$2:K$2>TODAY(),COLUMN(D4:K4),999999),999998))),"")
Type it in C4 and press Ctrl+Shift+Enter, because is an array formula.
Adapt it get the right part of the cell value if is needed.
答案2
得分: 0
根据您提供的示例数据，此代码可以复制您期望的结果。
Sub demo()Dim lRowCnt As Long, iColCnt As LongDim sDate As Date, iColStart As LongDim arr, res()arr = ActiveSheet.UsedRange.ValuelRowCnt = UBound(arr)iColCnt = UBound(arr, 2)sDate = #7/4/2023#'sDate = DateIf lRowCnt > 3 And iColCnt > 2 TheniColCnt = UBound(arr, 2)ReDim res(1 To lRowCnt - 3, 1 To 1)For j = 3 To iColCntIf arr(2, j) > sDate TheniColStart = jExit ForEnd IfNextIf iColStart > 0 ThenFor i = 4 To lRowCntFor j = iColStart To iColCntIf Len(Trim(arr(i, j))) > 0 Thenres(i - 3, 1) = Trim(Split(arr(i, j), ",")(1))End IfNextNextEnd IfEnd If[B4].Resize(lRowCnt - 3, 1).Value = resEnd Sub
希望这对您有所帮助。
英文:
Based on the sample data you provided, this code can replicate your expected outcome.
Sub demo()Dim lRowCnt As Long, iColCnt As LongDim sDate As Date, iColStart As LongDim arr, res()arr = ActiveSheet.UsedRange.ValuelRowCnt = UBound(arr)iColCnt = UBound(arr, 2)sDate = #7/4/2023#'sDate = DateIf lRowCnt > 3 And iColCnt > 2 TheniColCnt = UBound(arr, 2)ReDim res(1 To lRowCnt - 3, 1 To 1)For j = 3 To iColCntIf arr(2, j) > sDate TheniColStart = jExit ForEnd IfNextIf iColStart > 0 ThenFor i = 4 To lRowCntFor j = iColStart To iColCntIf Len(Trim(arr(i, j))) > 0 Thenres(i - 3, 1) = Trim(Split(arr(i, j), ",")(1))End IfNextNextEnd IfEnd If[B4].Resize(lRowCnt - 3, 1).Value = resEnd Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论