CODEWITHSUNDEEP
excelfunctionvba
sameer agarwal
sameer agarwal

Reputation: 1

VBA complie error when trying to pass range as argument

I am passing two range arguments in a function. One is working fine but second is giving compile error.

Here I am calling the function -

Set SourceRange = Sheets("QueryResult").Range("QueryResult")
Set DestinationRange = Sheets("TradeUnderlyingCptyWWRTemplate").Range("CounterpartyName")
Call PopulateDetails(23, SourceRange, DestinationRange, 2, 8)

And here is the function:

Function PopulateDetails(SourceColumns As Integer, Srce As Range, Destination As Range, DestinationColums As Integer, DestinationRows As Integer)

Dim CellName As String
Dim a, b, i As Integer

For b = 0 To DestinationRows - 1
    For a = 0 To DestinationColums - 1

        On Error GoTo Err:
        Sheets("TradeUnderlyingCptyWWRTemplate").Select
        Destination.Offset(b, a).Select
        CellName = Destination.Offset(b, a).Name.Name

        For i = 0 To SourceColumns - 1
            If (Destination.Offset(b, a).Name.Name = Srce.Offset(0, i).Value And Destination.Offset(b, a).Value = "") Then
                Destination.Offset(b, a).Value = Srce.Offset(1, i).Value
                Exit For
            End If
        Next
Err:
        On Error Resume Next
        Err.Clear
        CellName = ""
    Next
Next

End Function

However, it is giving compile error:

byref argument type mismatch

with SourceRange highlighted in the code.

When I change the function to only have four arguments (deleting Source argument), it works fine.

Upvotes: 0

Views: 1746

Answers (1)

neelsg
neelsg

Reputation: 4842

You need to change

Dim SourceRange, DestinationRange As Range

To

Dim SourceRange As Range
Dim DestinationRange As Range

Otherwise it is equivalent to

Dim SourceRange As Variant
Dim DestinationRange As Range

Upvotes: 4

Related Questions