需要编写excel函数来遍历工作簿中的所有工作表，然后返回单元格中的所有值

Question

此函数应从主工作表中选择票证编号，然后在所有其他工作表中查找同一票证的状态。然后更新主工作表中不同工作表中同一票证单元格中的所有状态。到目前为止，我写了以下代码，但不工作

Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
Dim sheets As Variant
Dim sheet As Variant
sheets = Array("Multiple Values single cell VL", "Sheet2")
For Each sheet In sheets
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
End If
Next i
Next sheet
SingleCellExtract = Left(Result, Len(Result) - 1)
End Function

Answer 1

将Range传递到函数时，该范围引用特定工作表上的特定范围。

在代码中，循环遍历一组工作表名称，但不使用该名称执行任何操作。

你的代码，重构的

Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
Dim sheets As Variant
Dim sheet As Variant
Dim ws As Worksheet
Dim SheetLookupRange As Range

sheets = Array("Multiple Values single cell VL", "Sheet2")
For Each sheet In sheets
Set ws = ThisWorkbook.Worksheets(sheet) '<~~ reference to the looped sheet
Set SheetLookupRange = ws.Range(LookupRange.Address) '<~~ reference to the range on on the looped sheet
For i = 1 To SheetLookupRange.Columns(1).Cells.Count
If SheetLookupRange.Cells(i, 1) = Lookupvalue Then
Result = Result & " " & SheetLookupRange.Cells(i, ColumnNumber) & ","
End If
Next i
Next sheet
SingleCellExtract = Left(Result, Len(Result) - 1)
End Function