英文:
Copy Cells from Separate Range Without #SPILL
问题
如果Sheet1!D2:D40中的任何单元格包含"COMPLETE"，我需要将该工作表中的单元格F(行)的内容复制到Sheet3!B(行)。我尝试过使用=IF，但这会导致39行的#SPILL!。在Sheet1上的条件得到满足之前，我需要Sheet3中的单元格保持为空白。之后，我需要它们根据需要逐行填充。例如，如果Sheet1!D6是"COMPLETE"，我希望Sheet1!F6复制到Sheet3!B1。然后，如果Sheet1!D17是"COMPLETE"，则Sheet1!F17应复制到Sheet3!B2，依此类推。
我显然是个新手（特别是在Excel/VBA方面），但我了解编程概念。我觉得可能有一些我不熟悉的数组函数可以完成这个任务。
我尝试过=IF(Sheet1!$D2:$D40 = "COMPLETE", Sheet1!$F2, "")，但没有用。
我在VBA中尝试了几种IF_THEN的变体，但结果都相同。
英文:
If any of the cells in Sheet1!D2:D40 contain "COMPLETE", I need cell F(row), from that sheet, to copy its contents to Sheet3!B(row). I've tried this using =IF, but that gives 39 rows of #SPILL!. I need the cells in Sheet3 to remain blank until said condition on Sheet1 is met. After that, I need them to fill in, row by row (as conditionally required). i.e., If Sheet1!D6 is "COMPLETE", I want Sheet1!F6 to copy to Sheet3!B1. Then, if Sheet1!D17 is "COMPLETE", then Sheet1!F17 should copy to Sheet3!B2, etc.
I'm clearly a rookie (ESPECIALLY with Excel/VBA), but I do understand programming concepts. I feel like there's probably some array function I'm unfamiliar with that could do this.
I've tried =IF(Sheet1!$D2:$D40 = "COMPLETE", Sheet1!$F2, "")
That was useless.
I tried several IF_THEN variations in VBA, but kept getting the same result.
答案1
得分: 0
你可以使用工作表公式获得匹配的结果。
=IFERROR(INDEX(Sheet1!$F$2:$F$40, FILTER(ROW(Sheet1!$D$2:$D$40), Sheet1!$D$2:$D$40="COMPLETE")-1, 1), "")
数组公式（按<Ctrl+Shift+Enter>）
=IFERROR(INDEX(Sheet1!$F$2:$F$40, SMALL(IF(Sheet1!$D$2:$D$40="COMPLETE", ROW(Sheet1!$D$2:$D$40)-1), ROW(1:40))), "")
VBA也是一种选项。
Sub demo()
Dim arr, res, idx
arr = Sheets("Sheet1").Range("D2:F40").Value
ReDim res(1 To UBound(arr), 1 To 1)
idx = 1
For i = 1 To UBound(arr)
If arr(i, 1) = "COMPLETE" Then
res(idx, 1) = arr(i, 3)
idx = idx + 1
End If
Next
If idx > 1 Then
Sheets("Sheet3").Cells(1, 2).Resize(idx - 1, 1).Value = res
End If
End Sub
英文:
You can get the matching result with worksheet formula.
=IFERROR(INDEX(Sheet1!$F$2:$F$40, FILTER(ROW(Sheet1!$D$2:$D$40), Sheet1!$D$2:$D$40="COMPLETE")-1, 1), "")
Array formula (Press <Ctrl+Shift+Enter>)
=IFERROR(INDEX(Sheet1!$F$2:$F$40, SMALL(IF(Sheet1!$D$2:$D$40="COMPLETE", ROW(Sheet1!$D$2:$D$40)-1), ROW(1:40))), "")
VBA is a option for you too.
Sub demo()
Dim arr, res, idx
arr = Sheets("Sheet1").Range("D2:F40").Value
ReDim res(1 To UBound(arr), 1 To 1)
idx = 1
For i = 1 To UBound(arr)
If arr(i, 1) = "COMPLETE" Then
res(idx, 1) = arr(i, 3)
idx = idx + 1
End If
Next
If idx > 1 Then
Sheets("Sheet3").Cells(1, 2).Resize(idx - 1, 1).Value = res
End If
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论