CODEWITHSUNDEEP
vb.net
Jose M.
Jose M.

Reputation: 2350

Using > Operator to compare values in excel range throwing exception. Operator not defined for Range

I updated my post and procedure and worked out all of the Operator errors. The procedure now works half as expected, instead of placing the values in column E, it places them on column J. If I change from column E to column B, then it places the values on G. So the issue is that somehow is shifting my values from my required column to 6 columns to the right.

Here is my whole code.

  Dim xlWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
Dim xlWSPosition As Excel.Worksheet = CType(CType(xlWB.Sheets("byPosition"), Excel.Worksheet), Excel.Worksheet)

                   With xlWSPosition

                .Application.ScreenUpdating = False

                '.Columns("E").Insert(Excel.XlDirection.xlDown)
                '.Range("E1").Value = "Exemption"
                '.Cells.EntireColumn.AutoFit()

                Dim colValue As Excel.Range
                Dim lngLr As Long
                lngLr = .Range("E" & .Rows.Count).End(Excel.XlDirection.xlUp).Row



                .Application.ScreenUpdating = True

                For Each colValue In .Range("F2:F" & .Range("F" & xlWSPosition.Rows.Count).End(XlDirection.xlUp).Row)

                    If colValue.Value > 0 Then

                        colValue.Cells.Range("E2:E" & lngLr).Value = "N"

                    Else

                        colValue.Cells.Range("E2:E" & lngLr).Value = "Y"


                    End If

                Next colValue

            End With
       End Sub

As you can see in the screenshot, the N value goes to column J instead of E and also, it seems that it goes an additional row down when looking for >0

Current Output

Should look like this:

Expected Results

Upvotes: 1

Views: 990

Answers (1)

user2480047
user2480047

Reputation:

You are getting this error message because of intending to perform an invalid operation with a Range (checking whether it is greater than zero). You should be doing that with the Value property, not with the whole Range. Here you have a correction of your code which will help you to understand things better:

For Each range In .Range("F2:F" & .Range("F" & xlWSPosition.Rows.Count).End(XlDirection.xlUp).Row)
   If(range IsNot Nothing AndAlso range.Value.Trim().Length > 0) Then
      Dim colValue As Double = DirectCast(range.Value, Double)
      If colValue > 0 Then
         .Cells(xlWSPosition.Rows, "E").Value = "N"
      Else
         .Cells(xlWSPosition.Rows, "E").Value = "Y"
      End If
   End If
Next

Note that I am assuming that the values in the given cells are of type Double; please adapt this bit (the type of colValue and the one in DirectCast) to the type you are expecting.

Upvotes: 1

Related Questions