Get number of cell

[trapo]

Hello everyone,
in a form vb.net I bind the project to an excel file adding the reference "Microsoft Excel 16.0 Object Library" and with tihs code:

Imports Microsoft.Office.Interop
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim a As Excel.Application
        a = CreateObject("EXCEL.APPLICATION")
        a.Visible = True
        a.Workbooks.Open("C:\Cartel1.xlsx")
    End Sub
End Class

I need to know the number of the cells based on a text I enter

So, If I want to search (for example) the word "hello" (lacated in a textbox) in the worksheet, the software have to put the number of the column and of the line, of the cells where the word hello is (in the worksheet), in a textbox.

How can I do? Thank you in advance

[TysonLPrice]

I've been looking all over. Take a look at this link but I cannot tell if it is what you want.

https://social.msdn.microsoft.com/Fo...orum=vbgeneral

[dday9]

@Tyson, I don't think the user is asking to get the column name but rather is asking to get the column/row index of cell with given text.

If that's the case then I think you will need to do the following:

1. Get the spreadsheet of the workbook by accessing the Sheets property (documentation) of the workbook
2. Create a range of cells by calling the Range method (documentation) on the worksheet
3. Search for the value in the range of cells by calling the Find method (documentation) on the range
4. If a result is returned, the get the Column (documentation) and Row (documentation) of the range returned by Find.

Following the documentation, here is an untested example where the result's X is the column and Y is the row:

Private Function FindCellByValue(workbook As WorkbookClass, value As String) As Point
    Dim sheets = workbook.Sheets
    If (sheets.Count = 0) Then
        Throw New Exception("The workbook does not contain any sheets.")
    End If

    Dim firstSheet = sheets(0)
    Dim columns = "abcdefghijklmnopqrstuvwxyz".ToCharArray().Select(Function(c) c.ToString())
    Dim result As Point
    For Each column In columns
        For row As Integer = 1 To 999
            Dim range = firstSheet.Range($"{column}{row}")
            Dim match = firstSheet.Find(value, , XlFindLookIn.xlValues, XlLookAt.xlWhole, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, False)

            If (match IsNot Nothing) Then
                result = New Point(match.Column, match.Row)
                Exit For
            End If
        Next
        If (result IsNot Nothing) Then
            Exit For
        End If
    Next

    Return result
End Function

[kareninstructor]

There is a free Excel library, SpreadSheetLight, on NuGet which can perform a case insensitive find against all used rows/columns in a WorkSheet.

Notes

• Used rows/columns are provided via GetWorksheetStatistics so no guess work for getting the used range
• Code is in a class to keep form code clean and reusable
• A class is used to represent findings, for debugging .ToString shows results while RowIndex, ColumnIndex provide the ability to access findings
• SpreadSheetLight has various methods to access cells and to perform various operations
• SpreadSheetLight is not dependent on a specific version of Excel installed but is constrained to .xlsx format.

Code sample

Option Infer On

Imports SpreadsheetLight

Namespace Classes
    Public Class SpreadSheetLightSearchOperations
        ''' <summary>
        ''' Find case insensitive token in all used rows/columns in a WorkSheet
        ''' </summary>
        ''' <param name="fileName">Excel file name (.xlsx only)</param>
        ''' <param name="sheetName">Sheet name to run against</param>
        ''' <param name="search">Token to find</param>
        ''' <returns>Name value tuple</returns>
        Public Shared Function Find(fileName As String, sheetName As String, search As String) _
            As (items As List(Of FoundItem), exception As Exception)

            Dim foundList = New List(Of FoundItem)()

            Try

                Using document = New SLDocument(fileName, sheetName)

                    Dim stats = document.GetWorksheetStatistics()

                    Dim columnIndex As Integer = 1
                    Do While columnIndex < stats.EndColumnIndex + 1

                        Dim rowIndex As Integer = 1

                        Do While rowIndex < stats.EndRowIndex + 1

                            If document.GetCellValueAsString(rowIndex, columnIndex).Equals(search, StringComparison.OrdinalIgnoreCase) Then

                                foundList.Add(New FoundItem() With {
                                                 .RowIndex = rowIndex,
                                                 .ColumnIndex = columnIndex,
                                                 .Column = SLConvert.ToColumnName(columnIndex)
                                                 })

                            End If

                            rowIndex += 1

                        Loop

                        columnIndex += 1

                    Loop

                End Using

                Return (foundList, Nothing)

            Catch exception As Exception
                Return (foundList, exception)
            End Try

        End Function
    End Class
    ''' <summary>
    ''' Result container for <see cref="Find"/> method
    ''' </summary>
    Public Class FoundItem
        Public Property RowIndex() As Integer
        Public Property Column() As String
        Public Property ColumnIndex() As Integer
        Public Overrides Function ToString() As String
            Return $"[{Column}:{RowIndex}]"
        End Function
    End Class
End Namespace

Example

Dim fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SomeFile.xlsx")
Dim sheetName = "SomeSheetName"

Dim Results = SpreadSheetLightSearchOperations.Find(fileName, sheetName, FindTextBox.Text)

If Results.exception IsNot Nothing Then
    For Each foundItem As FoundItem In Results.items
        Debug.WriteLine(foundItem)
    Next
End If