Results 1 to 4 of 4

Thread: Get number of cell

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2021
    Posts
    24

    Get number of cell

    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:

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,601

    Re: Get number of cell

    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
    Please remember next time...elections matter!

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,505

    Re: Get number of cell

    @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:
    Code:
    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
    "Code is like humor. When you have to explain it, itís bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,621

    Re: Get number of cell

    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

    Code:
    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

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width