using excel range doesn't like a string value

[Letschat]

Doing some excel work and I need to use the value of i to give me the range (different row each time around). how can I use a string value?


****** This one errors using the str1 value
Dim str1 as String
With sheet
For i = 2 To 10
str1 = "A" & Convert.ToString(i)
If .Range(str1).Value.ToString() = "Processed" Then 'errors
Msgbox("Working")
' End If
Next i
End with

****** This one works if I hard code a value but can’t do that
Dim str1 as String
With sheet
For i = 2 To 10
If .Range("A1").Value.ToString() = "Processed" Then 'works
Msgbox("Working")
' End If
Next i

[kareninstructor]

Here is a pattern to follow which foremost disposes of all used objects. Next up, if .Value is Null/Nothing this will throw and exception so use assertion to ensure you are not looking at a null cell.

Place these at the top of the class, form or code module

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Procedure

Public Sub OpenExcelForum(ByVal FileName As String, ByVal SheetName As String, ByVal RangeToScan As String())

    If IO.File.Exists(FileName) Then

        Dim Proceed As Boolean = False
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets


        '
        ' For/Next finds our sheet
        '
        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then
                Proceed = True
                Exit For
            End If

            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing

        Next

        If Proceed Then
            For Each cell In RangeToScan
                xlCells = xlWorkSheet.Range(cell)

                If xlCells.Value IsNot Nothing Then
                    If CStr(xlCells.Value) = "Processed" Then
                        MessageBox.Show($"{cell}")
                    End If
                End If


                ReleaseComObject(xlCells)
            Next

        Else
            MessageBox.Show(SheetName & " not found.")
        End If


        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlCells)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    Else
        MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
    End If
End Sub
Private Sub ReleaseComObject(ByVal obj As Object)
    Try
        If obj IsNot Nothing Then
            Marshal.ReleaseComObject(obj)
            obj = Nothing
        End If
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

Usage

Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Forum1.xlsx")
Dim range As String() = Enumerable.Range(1, 10).Select(Function(item) $"A{item}").ToArray

OpenExcelForum(fileName, "Sheet1", range)

[si_the_geek]

In addition to the advice above... If all you want is a single cell, there is no need to use a String for the address at all, you can simply use the .Cells property:

If .Cells(i, 1).Value.ToString() = "Processed" Then

[Letschat]

thanks for the response. I tried the .cells and got the well know error: Object reference not set to an instance of an object. So that is when I thought I would try the range. All I want to do is if that column has "processed" in it I want to delete the row. sounds simple lol

[Letschat]

Thanks Karen I should have mentioned I already had most of the excel usual code. I will check and compare though because I don't think my file is getting released. Still sitting in the task manager when done.

[si_the_geek]

thanks for the response. I tried the .cells and got the well know error: Object reference not set to an instance of an object.

That is likely to be because of this:

Here is a pattern to follow which foremost disposes of all used objects. Next up, if .Value is Null/Nothing this will throw and exception so use assertion to ensure you are not looking at a null cell.

So it is likely to be a good idea to use an extra If (or extra condition in the same If), eg:

If .Cells(i, 1).Value IsNot Nothing  AndAlso  .Cells(i, 1).Value.ToString() = "Processed" Then

(using AndAlso rather than And is important, because it avoids the error from checking the second condition)

[kareninstructor]

Thanks Karen I should have mentioned I already had most of the excel usual code. I will check and compare though because I don't think my file is getting released. Still sitting in the task manager when done.

This is a common issue when developers write code which interacts with Excel via automation. This is why I would write many lines of code to ensure this would not happen. The problem is non existing when using the following library SpreadsheetLight which I wrote examples for here.

Example for SpreadSheetLight, there are two methods, the first works by row index while the second works by column letter.

Basic break-down.
Create a new SLDocument passing in the file name to work with followed by the sheet name to work on which opens the file and points to the SheetName, in this case Sheet1. If the active sheet was say Sheet2 prior to opening the file now Sheet1 is (it's set back once finished). The using statement opens and closes the file.

We then iterate the rows, interrogating cells values using ToLower. Note the GetCellValueAsString is overloaded.

Imports SpreadsheetLight
Public Class vbForumsOperations
    Public Function GetProcessRowVersion1(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByVal ColumnIndex As Integer,
        ByVal MaxRow As Integer,
        ByVal Value As String,
        ByRef FoundRowIndex As Integer) As Boolean

        Using sl As New SLDocument(FileName, SheetName)
            For row As Integer = 1 To MaxRow
                If sl.GetCellValueAsString(row, ColumnIndex).ToLower = Value.ToLower Then
                    FoundRowIndex = row
                    Return True
                End If
            Next
        End Using

        Return False

    End Function
    Public Function GetProcessRowVersion2(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByVal Column As String,
        ByVal MaxRow As Integer,
        ByVal Value As String,
        ByRef FoundRowIndex As Integer) As Boolean

        Using sl As New SLDocument(FileName, SheetName)
            For row As Integer = 1 To MaxRow

                If sl.GetCellValueAsString($"{Column}{row}").ToLower = Value.ToLower Then
                    FoundRowIndex = row
                    Return True
                End If
            Next
        End Using

        Return False

    End Function

End Class

Form code

Public Class vbForumsFindString
    Private ops As New vbForumsOperations
    Private fileName As String = IO.Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "vbForumsExample.xlsx")
    Private findMe As String = "Processed"
    Private Sub cmdFindStringViaColumnIndex_Click(sender As Object, e As EventArgs) _
        Handles cmdFindStringViaColumnIndex.Click

        Dim RowIndex As Integer = 0

        If ops.GetProcessRowVersion1(fileName, "Sheet1", 1, 10, findMe, RowIndex) Then
            MessageBox.Show($"Found {findMe} on row {RowIndex}")
        Else
            MessageBox.Show($"Failed to find '{findMe}'")
        End If
    End Sub
    Private Sub cmdFindStringViaColumnLetter_Click(sender As Object, e As EventArgs) _
        Handles cmdFindStringViaColumnLetter.Click

        Dim RowIndex As Integer = 0
        If ops.GetProcessRowVersion2(fileName, "Sheet1", "A", 10, findMe, RowIndex) Then
            MessageBox.Show($"Found {findMe} on row {RowIndex}")
        Else
            MessageBox.Show($"Failed to find '{findMe}'")
        End If
    End Sub
End Class

Disadvantage of this library
Works only with .xlsx
Must read through the docs (downloadable in .chm) - my MSDN samples will get you started very fast for most operations.
The install has a dependency (I explain it in my code sample to select a specific version with an image to boot)

[Letschat]

Thanks! the .Cells(i, 1).Value IsNot Nothing AndAlso worked.