Getting "error: type mismatch" in VBA

Am getting Run-time error 13. Type Mismatch. But i cant figure out why. Any help?

Sub Separate_with_OR_without_comission()

Dim I As Integer
Dim WRng As Range
Dim NoRng As Range
Dim NameRgn As 开发者_如何学CRange
Dim TotalCRng As Range

Set WRng = Range("with_comission")
Set NoRng = Range("without_comission")
Set NameRgn = Range("total_comission_name")
Set TotalCRng = Range("ttotal_comission")

 For I = 1 To NameRgn.Rows.Count
    If TotalCRng.Rows(I) > 0 Then           // ERROR HERE
        WRng.Rows(I) = NameRgn.Rows(I)
    End If
    If TotalCRng.Rows(I) < 1 Then           // AND HERE
        NoRng.Rows(I) = NameRgn.Rows(I)
    End If
 Next I
End Sub

When i try to use other test cells is fine, the problem is with those.... but they are numbers inside "ttotal_comission" why does VBA takes it as something else?

---

The problem is that Rows(I) is returning a range object, not an integer value. You should fully qualify your statements like this: TotalCRng.Rows(I).Cells(1, 1).Value or possibly TotalCRng.Cells(1, 1).Value. Written as it is, Excel will return the value from Rows(I) if it happens to be a single cell, in which case the range's value property is called, but otherwise will raise the Type Mismatch error you're seeing because you're attempting to compare a range to an integer.

Example:

'no error
Debug.Print Sheet1.Range("B1")

'type mismatch error
Debug.Print Sheet1.Range("B1:B12")

Also, bear in mind that only the top left cell of a merged range will actually return a value.

---

You could use use a construct like this:

for each c in range("rangeName1")
    'if the source range is 3 columns to the right, same row'
    c = c.offset(0,3).value  
next c

---

If you are trying to check the value of the cells in each row, you need to loop through the cells and compare the values individually.

If the ranges are just single columns, instead of looping through each row, you can loop through each cell for the same effect.

 For I = 1 To NameRgn.Rows.Count
    For j = 1 to NameRgn.rows(I).cells.count
    If TotalCRng.Rows(I).cells(j).value > 0 Then           // ERROR HERE
        WRng.Rows(I).cells(j) = NameRgn.Rows(I).cells(j)
    End If
    If TotalCRng.Rows(I).cells(j).value < 1 Then           // AND HERE
        NoRng.Rows(I).cells(j) = NameRgn.Rows(I).cells(j)
    End If
    Next j
 Next I