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

Categories

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
1.7k views
Welcome To Ask or Share your Answers For Others

1 Answer

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")

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share

548k questions

547k answers

4 comments

86.3k users

...