CODEWITHSUNDEEP
vbaexcel
Lefty
Lefty

Reputation: 391

Excel VBA Range object as parameter

I've written this pretty short VBA macro in Excel which passes 2 Range objects to my own Function. This is the entire code:

Sub Cmp()

Dim OneMatch As Boolean
Dim NoMatches As Integer
Dim OneCell, TwoCell As Range

For Each OneCell In Range("X030Pols").Cells
   OneMatch = False: NoMatches = 0

   For Each TwoCell In Range("X206Pols").Cells

           TwoCell.Offset(0, 23).Value = 0

   Next

   For Each TwoCell In Range("X206Pols")
       If TwoCell.Offset(0, 22).Value = "" Then
           TwoCell.Offset(0, 23).Value = PolComp(OneCell, TwoCell)
           If TwoCell.Offset(0, 23).Value > 0 Then
               NoMatches = NoMatches + 1
           End If
       End If
   Next

    If NoMatches = 1 Then
       TwoCell.Offset(0, 22).Value = OneCell.Offset(0, -1).Value
       OneCell.Offset(0, 22).Value = TwoCell.Offset(0, -1).Value
    End If
Next

End Sub
Private Function PolComp(Acell As Range, Bcell As Range) As Integer

If Left(Acell.Offset(0, 1).Value, 4) = Left(Bcell.Offset(0, 1).Value, 4) Then
   PolComp = PolComp + 50
End If

If Acell.Offset(0, 6).Value = Bcell.Offset(0, 6).Value And Acell.Offset(0, 6).Value <> "" Then
   PolComp = PolComp + 50
End If

If Acell.Offset(0, 8).Value = Bcell.Offset(0, 8).Value Then
   PolComp = PolComp + 50
End If

End Function

but when I try to run it I get this error:

enter image description here

So OneCell is defined as a Range, my function uses a Range, but there's a mismatch. The error occurs as it tries to parse my code before it runs anything.

I could work around it but I'm more concerned about understanding what I've done wrong.

Upvotes: 0

Views: 1983

Answers (1)

JNevill
JNevill

Reputation: 50034

The problem is in your variable declaration:

Dim OneCell, TwoCell As Range

This is the same as writing:

Dim oneCell as Variant
Dim TwoCell as Range

Instead:

Dim oneCell as Range
Dim TwoCell as Range

Or:

Dim OneCell as Range, TwoCell As Range

So that VBA doesn't have to guess at your type.

Upvotes: 5

Related Questions