Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.9k views
in Technique[技术] by (71.8m points)

excel - VBA: How do you handle passing Sheet2!A:A through function parameters?

So I'm aware that Sheets2!A:A Refers specifically to the whole of column A within the Sheet 2 workbook. However, how do you actually process this when it's passed through a function?

Function Function1(cellValue As Variant, cellList As Range) As Variant
Dim cellContent As Variant
Dim list As Range
Dim i As Integer
cellContent = Sheets("Sheet1").Range(CStr(cellValue)).Value2
list = Range(cellList).Value2
For i = 1 To list
    If i = cellContent Then
        Function1 = "Found"
    Else
        Function1 = "Unfound"
    End If
Next i

End Function

How would you parse Sheets2!A:A so that it uses the sheet specified and then the range of values in A:A. I'm using a function so therefore it's effectively being passed through by the user as

=@Function1(A2,Sheet2!A:A)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

the range is already a range:

Function Function1(cellValue As range, cellList As Range) As Variant
Dim cellContent As Variant
Dim list As Variant
Dim i As Long
cellContent = cellvalue.Value2
list = intersect(cellList.parent.usedrange,celllist).Value2
Function1 = "Unfound"
For i = 1 To ubound(list,1)
    If list(i,1) = cellContent Then
        Function1 = "Found"
        Exit Function
    End If
Next i
End Function

But this just reinvents MATCH:

=IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),"Found","Unfound")

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...