Exception: Conversion from string "<text>" to type 'Integer' is not valid

Question

Edit: I had a piece of code which used to work almost as it should, when suddenly it started to throw a dbnull exception (which wasn't there before). Now, thanks to @david sdot, I got the colouring code inside the program working again.

Here is the working colouring code

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

    'If Not IsNothing(DataGridView2.Rows("As (Arsen)")) Then

    'Kategorier i kolonner (vanlig)
    'As
    Dim UL1As As Double = 8
    Dim UL2As As Double = 20
    Dim UL3As As Double = 50
    Dim Ul4As As Double = 600
    Dim Ul5As As Double = 1000

    If Me.DataGridView2.CurrentRow.Cells(0).Value Is DBNull.Value Then
        MessageBox.Show("Cellen er tom.")
    Else

        For i As Integer = 0 To Me.DataGridView2.Rows.Count - 1
            If Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL1As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.DodgerBlue
            ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL1As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL2As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.LawnGreen
            ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL2As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL3As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.Yellow
            ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL3As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul4As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.Orange
            ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul4As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul5As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.Red
            ElseIf Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul5As Then
                Me.DataGridView2.Rows(i).Cells("As (Arsen)").Style.BackColor = Color.BlueViolet
            End If
        Next
         End If
     End Sub

And the result looks like this:

I also want to export to Excel with color, but I cannot get the same logic to work for the export code.

Private Sub ExportToExcel()
    ' Creating a Excel object.
    Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
    Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
    Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

    Try

        worksheet = workbook.ActiveSheet

        worksheet.Name = "ExportedFromDatGrid"

        Dim cellRowIndex As Integer = 1
        Dim cellColumnIndex As Integer = 1


        'Loop through each row and read value from each column.
        For i As Integer = 0 To DataGridView2.Rows.Count - 2
            For j As Integer = 0 To DataGridView2.Columns.Count - 1
                ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                If cellRowIndex = 1 Then
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Columns(j).HeaderText
                Else
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
                End If
                cellColumnIndex += 1
            Next
            cellColumnIndex = 1
            cellRowIndex += 1
        Next

        If Not IsNothing(DataGridView2.Rows("As (Arsen)")) Then

            'As
            Dim UL1As As Double = 8
            Dim UL2As As Double = 20
            Dim UL3As As Double = 50
            Dim Ul4As As Double = 600
            Dim Ul5As As Double = 1000

            'Gir nå feilmeldingen 'Conversion from string "As (Arsen)" to type 'Integer' is not valid'

            For i As Integer = 0 To DataGridView2.Rows.Count - 2
                'For j As Integer = 0 To DataGridView2.Columns.Count - 1
                If DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL1As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL1As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL2As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL2As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL3As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL3As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul4As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul4As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul5As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul5As Then
                    worksheet.Cells(i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
                End If
                'cellColumnIndex += 1
                'Next
                cellColumnIndex = 1
                cellRowIndex += 1
            Next


        Else
            MessageBox.Show("Cellen er tom")
            Exit Sub
        End If
        'Getting the location And file name of the excel to save from user.
        Dim saveDialog As New SaveFileDialog()
        saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
        saveDialog.FilterIndex = 2

        If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            workbook.SaveAs(saveDialog.FileName)
            MessageBox.Show("Export Successful")
        End If
    Catch ex As System.Exception
        MessageBox.Show(ex.Message)
    Finally
        excel.Quit()
        workbook = Nothing
        excel = Nothing
    End Try

End Sub

Ideas, anyone?

Answer 1

This code solved the problem:

            'As
        Dim UL1As As Double = 8
        Dim UL2As As Double = 20
        Dim UL3As As Double = 50
        Dim Ul4As As Double = 600
        Dim Ul5As As Double = 1000

        For i As Integer = 0 To DataGridView2.Rows.Count - 2
            For j As Integer = 2 To 2
                If DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL1As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL1As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL2As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL2As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < UL3As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= UL3As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul4As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul4As And Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value < Ul5As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                ElseIf DataGridView2.Rows(i).Cells("As (Arsen)").Value >= Ul5As Then
                    worksheet.Cells(i + 1, j + 1).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
                End If
                cellColumnIndex += 1
            Next
            cellColumnIndex = 1
            cellRowIndex += 1
        Next

I had to hardcode the j index to apply the formatting to the correct row. Annoying, but it's a place to work from.

Now I only have to find out how to export header row without removing the first data row ...

Answer 2

Your Problem is here:

Me.DataGridView2.Rows(i).Cells("As (Arsen)").Value

Cells expects as an Integer as Index (like .Cells(1)). And as the error states As (Arsen) can not be converted to an Integer.

So either you you know the Cells index or you have to look for it in the first row to find it.