A for next problem
A for next problem
A for next problem
A for next problem
A for next problem
A for next problem A for next problem A for next problem A for next problem A for next problem A for next problem A for next problem A for next problem
A for next problem A for next problem
A for next problem
Go Back  Xtreme Visual Basic Talk > > > A for next problem


Reply
 
Thread Tools Display Modes
  #1  
Old 09-14-2011, 02:15 AM
locksmith46 locksmith46 is offline
Newcomer
 
Join Date: Jun 2011
Posts: 8
Default A for next problem


Hi Im using vb 2008 so that when i press a button on my form it will give me a list of the cells in a worksheet that contain a specific text. My problem is that i have managed to get my code to find the first cell in a range but that is all it finds I cannot seem to get findnext or findall to work. Here is my code



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

Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim excelApp As New Excel.Application
        'excelApp.Visible = True

        Dim workbook As Excel.Workbook = excelApp.Workbooks.Open("C:\Users\mick\Desktop\EXCELTEST\ April 14th Schedule TEST.xls")
        workbook.Activate()

        Dim worksheet As Excel.Worksheet = workbook.Worksheets(1)

        With worksheet.Range("A1:Y296")
            Dim c As Excel.Range = _
                .Find("john", _
                        LookIn:=Excel.XlFindLookIn.xlValues, _
                        LookAt:=Excel.XlLookAt.xlWhole)

            If Not c Is Nothing Then
                'MsgBox(c.Row & "," & c.Column)
                TextBox1.Text = c.Row
                TextBox2.Text = c.Column
                

                
            End If
        End With
This will get me the answer 24 16 in the text box, which is the cell containing the 1st occurence of the word "john" ,but i cant get a findall or find next to work i just start to get the cryptic error such as object reference not set to instance of object.

I have scoured the internet and nothing seems to work any help would be appreciated
Reply With Quote
  #2  
Old 09-14-2011, 09:28 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

You need to use the method FíndNext of the Range Object. At present I'm on a computer without VS but I guess You can be able to resolve it. If not, post back.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #3  
Old 09-16-2011, 08:55 AM
locksmith46 locksmith46 is offline
Newcomer
 
Join Date: Jun 2011
Posts: 8
Default

HI guys just for your information i managed to cure the problem using the following code . the main problem was a syntax error where i previously had excel.range it needed to be worksheet.range once changed it worked

Code:
Dim excelApp As New Excel.Application
       
        Dim workbook As Excel.Workbook = excelApp.Workbooks.Open(TextBox1.Text)
        workbook.Activate()

        Dim worksheet As Excel.Worksheet = workbook.Worksheets(1)

        Dim FoundCell As Excel.Range
        Dim LastCell As Excel.Range
       
        With worksheet.Range("A1:Y220")
            LastCell = .Cells(.Cells.Count)
        End With
        FoundCell = worksheet.Range("A1:Y220").Find(What:=TextBox2.Text, After:=LastCell)

        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
        End If
        Do Until FoundCell Is Nothing
            'MsgBox("FOUND LOCKMAN AT CELL:" & FoundCell.Address)
            TextBox1.Text = FoundCell.Address
            ListBox1.Items.Add(TextBox1.Text)
            FoundCell = worksheet.Range("A1:Y220").FindNext(After:=FoundCell)
            If FoundCell.Address = FirstAddr Then
                Exit Do
            End If
        Loop
       
        workbook.Close()
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
A for next problem
A for next problem
A for next problem A for next problem
A for next problem
A for next problem
A for next problem A for next problem A for next problem A for next problem A for next problem A for next problem A for next problem
A for next problem
A for next problem
 
A for next problem
A for next problem
 
-->