CODEWITHSUNDEEP
excelvba
den
den

Reputation: 709

return multiple values in different cells in excel using VBA

    Function toto() As Variant

    For Each cell In Range("N1:N45")
        found = 0
        For Each cell2 In Range("B:B")
            If cell.Value = cell2.Value Then
                found = 1
            End If
            If found = 1 Then
                toto = cell.Value
                Exit For
            End If
        Next
     Next    
End Function

I want to return multiple values in excel. how should i change that?

Upvotes: 0

Views: 5303

Answers (3)

den
den

Reputation: 709

Function foo()
        y = 3
        For Each cell In Range("B2:B64")
            found = 0
            For Each cell2 In Intersect(ActiveSheet.UsedRange, Range("A:A"))
                If cell.Value = cell2.Value Then
                    found = 1
                    Exit For
                End If
            Next
            If found = 0 Then
                y = y + 1
                Cells(y, "D").Value = cell.Value
            End If
         Next   
End Function

And then had a Sub function to call foo

Sub foo2()
    Range("D4:D80").ClearContents
    toto
End Sub

Upvotes: 0

Automate This
Automate This

Reputation: 31364

One way is to use a global variable to count the number of matches found. Declare this variable at the top of your code and modify your function like this:

Dim found As Integer

Function toto() As Variant
    Dim count As Integer
    count = 0

    For Each cell In Range("N1:N45")
        For Each cell2 In Range("B:B")
            If cell.Value = cell2.Value Then
                count = count + 1

                'Skip over previously returned values
                If count > found Then
                    found = found + 1
                    toto = cell.Value
                    Exit Function
                End If
            End If
        Next
     Next
End Function

The global variable will retain it's value even when the function is not in use. This means that each time you call the toto() function it will skip past the previously found value. This also means that you have to reset if if you want to start over.

Here is a test sub. The first call will find the first match. The second call will find the second match because we didn't reset found to zero between calls.

Sub test()
    'Reset function
    found = 0
    MsgBox (toto())

    MsgBox (toto())
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Function toto() As Variant
    For Each cell In Range("N1:N45")
        found = 0
        For Each cell2 In Intersect(ActiveSheet.UsedRange, Range("B:B"))
            If cell.Value = cell2.Value Then
                found = 1
            End If
            If found = 1 Then
                toto = toto & ", " & cell.Value
                found = 0
            End If
        Next
     Next
End Function

Upvotes: 0

Related Questions