Loop down excel column

[Alex_W]

Hi all,

I'm trying to loop down columb A in an Excel file looking for numbers that the program will ultimatly match with what it finds down columb A in another file.

Right now I can't get it to loop down and find "789" which I haven as line 3. Any help appreciated:

Dim xlsApp As Excel.Application
        Dim xlsWB As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet
        Dim xlsCell As Excel.Range
        Dim i As Integer = 1

        xlsApp = New Excel.Application
        xlsApp.Visible = False
        xlsWB = xlsApp.Workbooks.Open(lblFile1Location.Text)
        xlsSheet = xlsWB.Worksheets(1)
        xlsCell = xlsSheet.Range("A" & i)

        Do Until xlsCell Is Nothing
            If xlsCell Is "789" Then
                MessageBox.Show("789 found")
                Exit Do
            Else
                i = i + 1
            End If
        Loop

 

[ggunter]

The line of code setting xlsCell to the Range is outside of your loop. You are incrementing "i" but not resetting xlsCell to the new Range.

 

[Alex_W]

Thanks for the reply ggunder, I have made some changes but this code just doesnt do anything. I want it to loop down the rows in columb A until it finds "789" (which is in A3). The loop code is also locking the file even after closing the progam:

Dim xlsApp As Excel.Application
                Dim xlsWBFile1 As Excel.Workbook
                Dim xlsSheetFile1 As Excel.Worksheet
                Dim xlsCellFile1 As Excel.Range
                Dim File1Counter As Integer = 1
                Dim xlsWBFile2 As Excel.Workbook
                Dim xlsSheetFile2 As Excel.Worksheet
                Dim xlsCellFile2 As Excel.Range
                Dim File2Counter As Integer = 1

                xlsApp = New Excel.Application
                xlsApp.Visible = False
                xlsWBFile1 = xlsApp.Workbooks.Open(lblFile1Location.Text)
                xlsSheetFile1 = xlsWBFile1.Worksheets(1)
                xlsCellFile1 = xlsSheetFile1.Range("A1")
                xlsWBFile2 = xlsApp.Workbooks.Open(lblFile2Location.Text)
                xlsSheetFile2 = xlsWBFile2.Worksheets(1)
                xlsCellFile2 = xlsSheetFile2.Range("A1")

                Do Until xlsCellFile1.Value Is Nothing
                    If xlsCellFile1.Value Is "789" Then
                        MessageBox.Show("789 found")
                        Exit Do
                    Else
                        File1Counter = File1Counter + 1
                        xlsCellFile1 = xlsSheetFile1.Range("A" & File1Counter)
                    End If
                Loop
xlsApp = Nothing

Any ideas?

 

[ggunter]

I tested this and it works for both string and integer values.

Imports Microsoft.Office.Interop.Excel

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlsApp As Microsoft.Office.Interop.Excel.Application
        Dim xlsWBFile1 As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsSheetFile1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlsCellFile1 As Microsoft.Office.Interop.Excel.Range
        Dim File1Counter As Int32 = 1

        xlsApp = New Microsoft.Office.Interop.Excel.Application
        xlsApp.Visible = False
        Dim wbkPath As String = "C:\z_GG Project\_Library\z_Forum\Test_001\bin\Debug\Book1.xls"
        xlsWBFile1 = xlsApp.Workbooks.Open(wbkPath)
        xlsSheetFile1 = CType(xlsWBFile1.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
        xlsCellFile1 = xlsSheetFile1.Range("A1")

        Do Until xlsCellFile1.Value Is Nothing

            If CInt(xlsCellFile1.Value) = 789 Then
                MessageBox.Show("Number 789 found on row " & File1Counter.ToString, _
                "Number Found", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ElseIf xlsCellFile1.Value Is "789" Then
                MessageBox.Show("String 789 found on row " & File1Counter.ToString, _
                "String Found", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

            File1Counter += 1
            xlsCellFile1 = xlsSheetFile1.Range("A" & File1Counter.ToString)

        Loop

        xlsCellFile1 = Nothing
        xlsSheetFile1 = Nothing
        xlsWBFile1 = Nothing
        xlsApp = Nothing

    End Sub

 

[Alex_W]

Thanks very much for the code! I've taylored it slightly because I have 2 excel files, 1st with codes in column A which I want the program to loop down columb A in the 2nd file, and copy columb B contents back to column B in the 1st file when it finds an ID match.

Basically File 1 will have address ID's and will find them in File two, copying the addresses back when the ID matches are made between the column A's.

The section of code copying the data across in the Do Loop seems to be the problem here:

Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click

        Dim xlsApp As Microsoft.Office.Interop.Excel.Application
        Dim xlsWBFile1 As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsWBFile2 As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsSheetFile1 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlsSheetFile2 As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlsCellFile1 As Microsoft.Office.Interop.Excel.Range
        Dim xlsCellFile2 As Microsoft.Office.Interop.Excel.Range
        Dim File1Counter As Int32 = 1
        Dim File2Counter As Int32 = 1
        Dim wbkPath1 As String = lblFile1Location.Text
        Dim wbkPath2 As String = lblFile2Location.Text

        xlsApp = New Microsoft.Office.Interop.Excel.Application
        xlsApp.Visible = False
        xlsWBFile1 = xlsApp.Workbooks.Open(wbkPath1)
        xlsWBFile2 = xlsApp.Workbooks.Open(wbkPath2)
        xlsSheetFile1 = CType(xlsWBFile1.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
        xlsSheetFile2 = CType(xlsWBFile2.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
        xlsCellFile1 = xlsSheetFile1.Range("A" & File1Counter)
        xlsCellFile2 = xlsSheetFile2.Range("A" & File2Counter)

        Do Until xlsCellFile1.Value Is Nothing
            If CInt(xlsCellFile1.Value) = xlsCellFile2.Value Then
                xlsCellFile1.Value = xlsCellFile2.Value
                'ElseIf xlsCellFile1.Value Is "789" Then
            Else
                File2Counter += 1
            End If

            xlsCellFile2 = xlsSheetFile2.Range("A" & File2Counter.ToString)
        Loop

        xlsCellFile1 = Nothing
        xlsSheetFile1 = Nothing
        xlsWBFile1 = Nothing
        xlsApp = Nothing

    End Sub

 

[Alex_W]

Ah, solved by copying using Value2 instead of value.