Read out values from a specific row
Read out values from a specific row
Read out values from a specific row
Read out values from a specific row
Read out values from a specific row
Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row
Read out values from a specific row Read out values from a specific row
Read out values from a specific row
Go Back  Xtreme Visual Basic Talk > > > Read out values from a specific row


Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2011, 08:12 AM
maj3stro maj3stro is offline
Newcomer
 
Join Date: Sep 2011
Posts: 3
Unhappy Read out values from a specific row


Hello @ everyone from xtremevbtalk.com.

I am currently stuck because i don't understand how my problem could be solved.
The problem...

I want to look up a value in column B and if the value of the cell in column b matches my searched value i want to look up a value in another column. If both values match the values i am searching for, i want to exit the while loop.
If there is no row that contains both my values in the range of cells, that contain any value in column b i want to exit my while loop.

I am literally crying, because i just can't get this done.
As you can see i am trying to do this via a for each loop.

there are so many things that didn't work so far...
the rows 3 to 6 are filled, but rows 7 to 10 are empty. row 11 is filled.
the counter returns the value for counter, that shows, that only not empty cells or rows were counted. I just don't get it. A already wrote, that if the cell.value is nothing, counter shoul be incremented, but this didn't seem to work.

I also tried the findmethod in vba, but couldn't get it to work.

I would be so thankful, if anybody could help me to achieve my goal.




my code:
Code:
   Sub GetSumme()
        '====================================================================================================
        ' Dieses Sub summiert die zu kaufenden und verkaufenden Mengen. Wichtig für den korrekten Start
        ' im aktuellen Kästchen ist der Wert, der in "bearbeitet" steht. Das ist ein fortlaufender Wert, der
        ' täglich um 6 Uhr morgens wieder auf null gesetzt wird.
        ' Für die Summierung der Kauf und Verkaufswerte habe wird eine for-schleife über alle 100 Kästchen 
        ' genutzt. Sollten die Attribute in der if-Abfrage alle zutreffen wird die Kaufsumme bzw die
        ' Verkaufsumme aufsummiert. Anschließend wird die Mappe gesichert und geschlossen.
        ' Die Instanz der Excel-Application wird mit Quit() geschlossen. Das ist notwendig um hinterher
        ' keine Probleme beim öffnen selbiger von anderen Computern zu haben.
        '
        ' zeile = 5 + xlBlatt.Cells(2, 9).Value -->Gibt aktuelle Zeile an
        ' 
        '
        '========
        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlMappe = xlApp.Workbooks.Open("G:\PM_Gas\It-Support\Arne\Regelenergiehistory.xls")
        xlBlatt = xlMappe.Worksheets("Tabelle1")
        xlBlatt2 = xlMappe.Worksheets("Tabelle2")

        Dim Kaufsumme As Integer
        Dim Verkaufsumme As Integer

        Dim temp_buy As Integer
        Dim temp_sell As Integer

        temp_buy = 0
        temp_sell = 0

        Dim zeile As Integer
        Dim spalte As Integer
        Dim zeile2 As Integer
        Dim spalte2 As Integer
        Dim counter As Integer
        Dim isnull As Boolean = False
        Dim findCell As Microsoft.Office.Interop.Excel.Range
        Dim xlrng As Microsoft.Office.Interop.Excel.Range
        Dim myCell As Microsoft.Office.Interop.Excel.Range
        Dim lastCell As Microsoft.Office.Interop.Excel.Range

        found = False

        zeile = 5 + xlBlatt.Cells(2, 9).Value
        spalte = 3

        zeile2 = 3
        spalte2 = 2

        Kaufsumme = 0
        Verkaufsumme = 0
        counter = 0




        Dim VBSent_Range As Microsoft.Office.Interop.Excel.Range
        VBSent_Range = xlBlatt.Range("F5:F104")


        For zeile = 5 + xlBlatt.Cells(2, 9).Value To 104
            'Wenn vbsent = 0, die art = "O" und die Auftragsnummer > 0 ist, dann...
            If xlBlatt.Cells(zeile, spalte + 3).value = 0 And xlBlatt.Cells(zeile, spalte + 1).value = "O" And xlBlatt.Cells(zeile, spalte).Value > 0 Then
                ' Menge der aktuellen Zelle auslesen
                temp_buy = xlBlatt.Cells(zeile, spalte).Value
                ' Menge aufsummieren
                Kaufsumme = Kaufsumme + temp_buy
                ' Die Zelle im Reiter "VBSent" auf "1" setzen
                xlBlatt.Cells(zeile, spalte + 3).value = 1
                ' Die Anzahl der bearbeiteten Zellen um "1" erhöhen
                xlBlatt.Cells(2, 9) = xlBlatt.Cells(2, 9).Value + 1


                xlrng = xlBlatt2.Range("B3:B65536")
                Try
                    For Each cell In xlrng
                        If Format(cell.value, "dd.MM.yyyy") = Format(Calendar.TodayDate, "dd.MM.yyyy") And xlBlatt2.Cells(zeile2 + counter, spalte2 + 2).value.ToString = "Buy" Then
                            xlBlatt2.Cells(zeile2 + counter, spalte2 + 7).value = Kaufsumme
                            found = True
                            GoTo foundevaluate

                        Else : counter = counter + 1


                        End If

                    Next



foundevaluate:      If found = True Then
                        MsgBox("Found")
                    ElseIf False Then
                        MsgBox("Not Found")
                    End If

                Catch e As NullReferenceException
                    counter = counter + 1

                End Try

            ElseIf xlBlatt.Cells(zeile, spalte + 3).value = 0 And xlBlatt.Cells(zeile, spalte + 1).value = "P" And xlBlatt.Cells(zeile, spalte).Value > 0 Then
                temp_sell = xlBlatt.Cells(zeile, spalte).value
                Verkaufsumme = Verkaufsumme + temp_sell
                xlBlatt.Cells(zeile, spalte + 3).value = 1
                xlBlatt.Cells(2, 9) = xlBlatt.Cells(2, 9).Value + 1

            End If

        Next

        MsgBox("counter: " & counter)
        MsgBox("Die Summe der Verkäufe beträgt: " & Verkaufsumme)
        MsgBox("Die Summe der Käufe beträgt: " & Kaufsumme)

        lblKaufsumme.Text = Kaufsumme
        lblVerkaufsumme.Text = Verkaufsumme

        xlMappe.Save()
        xlMappe.Close()
        xlApp.Quit()

    End Sub

Last edited by Gruff; 09-06-2011 at 11:15 AM.
Reply With Quote
  #2  
Old 09-06-2011, 10:42 AM
Gruff's Avatar
GruffRead out values from a specific row Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

Welcome to the forum maj3stro. Be sure to read the forum Posting Guidelines.

Automating Office Product from VB.NET is not as easy as it was under VB5 or VB6.

Step 1. If you add the Excel Reference under your project properties | References then you simply use the Import keyword with Excel and you are good to go.

Step 2. While I am sure you could use the Range.Find Method you can also create your own if that is the way you want to go. Create a Function the does the work and your If statements become much cleaner and easier to understand.

In my example I am returning the row that the item was found under. If nothing was found then a zero is returned.

Step 3. Be sure to clean up your automated session objects. This is necessary with VB.NET if you do not want an Excel session floating in memory.

Step 4. I do not include error handling in my example but you should be using it.

This example searches for two items on different columns on different rows.
Code:
Option Strict Off Imports Excel Public Class Form1 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click Const Col_B as integer = 2 Const Col_M as integer = 13 Dim oApp As New Excel.Application Dim oBook As Workbook = oApp.Workbooks.Open("C:\AAA\Test1.xls") Dim oSheet As Worksheet = oBook.Worksheets("Sheet1") Dim bResult As Boolean = False ' Look for values If InColumn(oSheet, Col_B, TextBox1.Text) > 0 Then If InColumn(oSheet, Col_M, TextBox2.Text) > 0 Then bResult = True End If End If If bResult Then MessageBox.Show("Both values found") Else MessageBox.Show("Neither value found") End If oBook.Close() oApp.Quit() 'Clean Excel session GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() FinalReleaseAnyComObject(oSheet) FinalReleaseAnyComObject(oBook) FinalReleaseAnyComObject(oApp) End Sub Private Function InColumn(ByVal oSheet As Worksheet, _ ByVal ColumnNum As Integer, _ ByVal SearchText As String) As Integer 'Return Row Number if found Dim nFoundRow As Integer = 0 For nRow As Integer = 1 To oSheet.Rows.Count - 1 Dim sValue As String = oSheet.Cells(nRow, ColumnNum).Text.ToLower If sValue = SearchText.ToLower Then nFoundRow = nRow ' Item found so no need to seach further Exit For End If Next Return nFoundRow End Function Shared Sub FinalReleaseAnyComObject(Of TComType)(ByRef rcw As TComType) Dim tempVar As TComType = rcw rcw = Nothing System.Runtime.InteropServices.Marshal.FinalReleaseComObject(tempVar) End Sub End Class

P.S. When posting your own code be sure to use the underscore character (VB Line continuation character) to wrap your long code lines. Otherwise it makes the whole thread scroll off the screen left and right. This makes it difficult for anyone to read.
__________________
Burn the land and boil the sea
You can't take the sky from me


~T

Last edited by Gruff; 09-07-2011 at 12:25 PM.
Reply With Quote
  #3  
Old 09-06-2011, 03:43 PM
maj3stro maj3stro is offline
Newcomer
 
Join Date: Sep 2011
Posts: 3
Default

Hello Gruff, let me first thank you. I am really astonished, that you could understand what i asked to know, as i for myself can barely understand it... I was so depressed from my first experience of being stuck at programming so i just wrote down what came to my mind :/

From your post i gained a huge plus in knowledge! I really do have the problem, that excel is floating my memory...
I will again try tomorrow and hopefully succeed in writing a cleaner and overall better code. I will post that code again here.

oh and i noticed, that "kaufsumme" and "verkaufsumme" aren't fully counted until the very end in my code...

P.S: It's true, some line could really use a line break. I will make sure next time it's easier to read.

greetings
maj3stro =)
Reply With Quote
  #4  
Old 09-07-2011, 12:24 PM
Gruff's Avatar
GruffRead out values from a specific row Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

maj3stro,

Actually I didn't read you code at all. I just wrote an example that shows one way to do what you asked.

If you need to capture the found rows you could just use a couple of variables.

Code:
' Look for values Dim B_Row as integer = InColumn(oSheet, Col_B, TextBox1.Text) Dim M_Row as integer = InColumn(oSheet, Col_M, TextBox2.Text) If B_Row > 0 and M_Row > 0 Then bResult = True If bResult then ' Do something with B_Row and M_Row Else MessageBox.Show("Neither value found") End If
__________________
Burn the land and boil the sea
You can't take the sky from me


~T
Reply With Quote
  #5  
Old 09-08-2011, 04:46 AM
maj3stro maj3stro is offline
Newcomer
 
Join Date: Sep 2011
Posts: 3
Default

i did it via this code. Only Problem i still have is, that tihe code doesn't end, when there is nothing found. I am still trying to make it end, when there is a blank cell, but somehow it doesn't seem to work. I tied it like you can see in 3..........
There i have the problem, that he runs forever, because he never seems to recognize, that a/the cell is empty or = ""



1...............................
Sub GetSumme()
'===================================================================== ===============================
' Dieses Sub summiert die zu kaufenden und verkaufenden Mengen. Wichtig für den korrekten Start
' im aktuellen Kästchen ist der Wert, der in "bearbeitet" steht. Das ist ein fortlaufender Wert, der
' täglich um 6 Uhr morgens wieder auf null gesetzt wird.
' Für die Summierung der Kauf und Verkaufswerte habe wird eine for-schleife über alle 100 Kästchen
' genutzt. Sollten die Attribute in der if-Abfrage alle zutreffen wird die Kaufsumme bzw die
' Verkaufsumme aufsummiert. Anschließend wird die Mappe gesichert und geschlossen.
' Die Instanz der Excel-Application wird mit Quit() geschlossen. Das ist notwendig um hinterher
' keine Probleme beim öffnen selbiger von anderen Computern zu haben.
'
' zeile = 5 + xlBlatt.Cells(2, 9).Value -->Gibt aktuelle Zeile an
'
' TODO: Langen Text durch Funktion ersetzen!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'
'===================================================================== ===============================
xlMappe = xlApp.Workbooks.Open("G:\PM_Gas\It-Support\Arne\Regelenergiehistory.xls")
xlBlatt = xlMappe.Worksheets("Tabelle1")
xlBlatt2 = xlMappe.Worksheets("Tabelle2")

Dim Kaufsumme As Integer
Dim Verkaufsumme As Integer
Dim temp_buy As Integer
Dim temp_sell As Integer
Dim zeile As Integer
Dim spalte As Integer
Dim zeile2 As Integer
Dim spalte2 As Integer
Dim counter As Integer
Dim isnull As Boolean = False
Dim maxNumberOfRows As Integer
Dim VBSent_Range As Microsoft.Office.Interop.Excel.Range

temp_buy = 0
temp_sell = 0
maxNumberOfRows = 65536
found = False

zeile = 5 + xlBlatt.Cells(2, 9).Value
spalte = 3

zeile2 = 3
spalte2 = 2

Kaufsumme = 0
Verkaufsumme = 0
counter = 0
VBSent_Range = xlBlatt.Range("F5:F104")


Try
For zeile = 5 + xlBlatt.Cells(2, 9).Value To 104
If Not xlBlatt.Cells(zeile, spalte - 1).value Is Nothing And _
xlBlatt.Cells(zeile, spalte).value > 0 And _
xlBlatt.Cells(zeile, spalte + 1).value.ToString = "O" And _
xlBlatt.Cells(zeile, spalte + 2).value = 1 Then
'Wenn vbsent = 0, die art = "O" und die Auftragsnummer > 0 ist, dann...

' Menge der aktuellen Zelle auslesen
temp_buy = xlBlatt.Cells(zeile, spalte).Value
' Menge aufsummieren
Kaufsumme = Kaufsumme + temp_buy
' Die Zelle im Reiter "VBSent" auf "1" setzen
xlBlatt.Cells(zeile, spalte + 3).value = 1
' Die Anzahl der bearbeiteten Zellen um "1" erhöhen
xlBlatt.Cells(2, 9) = xlBlatt.Cells(2, 9).Value + 1


'While xlBlatt2.Cells(maxNumberOfRows, 2).value Is Nothing
' maxNumberOfRows = maxNumberOfRows - 1


'End While

'MsgBox("Max number of Rows" & maxNumberOfRows)

ElseIf Not xlBlatt.Cells(zeile, spalte - 1).value Is Nothing And _
xlBlatt.Cells(zeile, spalte).value > 0 And _
xlBlatt.Cells(zeile, spalte + 1).value.ToString = "P" And _
xlBlatt.Cells(zeile, spalte + 2).value = 1 Then
temp_sell = xlBlatt.Cells(zeile, spalte).Value
' Menge aufsummieren
Verkaufsumme = Verkaufsumme + temp_sell
' Die Zelle im Reiter "VBSent" auf "1" setzen
xlBlatt.Cells(zeile, spalte + 3).value = 1
' Die Anzahl der bearbeiteten Zellen um "1" erhöhen
xlBlatt.Cells(2, 9) = xlBlatt.Cells(2, 9).Value + 1

End If

Next

Catch e As NullReferenceException
End Try

xlMappe.Save()


MsgBox("Die Summe der Verkäufe beträgt: " & Verkaufsumme)
MsgBox("Die Summe der Käufe beträgt: " & Kaufsumme)

SummenSchreiben(Verkaufsumme, "Sell", zeile2, spalte2, maxNumberOfRows, counter)
SummenSchreiben(Kaufsumme, "Buy", zeile2, spalte2, maxNumberOfRows, counter)

lblKaufsumme.Text = Kaufsumme
lblVerkaufsumme.Text = Verkaufsumme



End Sub








2........................................
Function SummenSchreiben(ByVal Summe As Integer, ByVal art As String, ByVal zeile2 As Integer, ByVal spalte2 As Integer, ByVal maxNumberOfRows As Integer, ByVal counter As Integer)
'===================================================================== ======================================
' Hier werden die Summen in die Exceltabelle eingetragen. Die richtige Zeile wird über eine
' For-Schleife rausgesucht
'===================================================================== ======================================
xlMappe = xlApp.Workbooks.Open(Regelenergiehistory)
xlBlatt = xlMappe.Worksheets("Tabelle1")
xlBlatt2 = xlMappe.Worksheets("Tabelle2")

Try


For row = zeile2 To xlBlatt2.Rows.Count - 1
If Format(xlBlatt2.Cells(row, spalte2).Value, "dd.MM.yyyy") = Format(Calendar.TodayDate, "dd.MM.yyyy") Then
If xlBlatt2.Cells(row, spalte2 + 2).Value.ToString = art Then
xlBlatt2.Cells(row, spalte2 + 7).value = xlBlatt2.Cells(row, spalte2 + 7).value + Summe
Exit For
End If
End If
Next

Catch e As NullReferenceException
'===================================================================== =============
' Sollte der Wert einer Zelle eine NullreferenceException auslösen,
' dann wird Summenschreiben erneut aufgerufen
'===================================================================== =============
SummenSchreiben(Summe, art, zeile2 + 1, spalte2, maxNumberOfRows, counter)
End Try

xlMappe.Save()
xlMappe.Close()

End Function



3..........................................


While found = False And Not xlBlatt2.Cells(3, spalte).value.ToString = ""
If Format(xlBlatt2.Cells(row, spalte).value, "dd.MM.yyyy") = _
Format(Calendar.TodayDate, "dd.MM.yyyy") Then
If xlBlatt2.Cells(row, spalte + 2).value.ToString = "Buy" Then
dateFound = True

lblGasart_Today_Buy.Text = xlBlatt2.Cells(row, spalte + 1).value
lblMaxMenge_Today_Buy.Text = xlBlatt2.Cells(row, spalte + 3).value
lblKey_Today_Buy.Text = xlBlatt2.Cells(row, spalte + 4).value
ListBox_priorities_Today_Buy.Text = xlBlatt2.Cells(row, spalte + 5).value

lblGasart_Today_Sell.Text = xlBlatt2.Cells(row + 1, spalte + 1).value
lblMaxMenge_Today_Sell.Text = xlBlatt2.Cells(row + 1, spalte + 3).value
lblKey_Today_Sell.Text = xlBlatt2.Cells(row + 1, spalte + 4).value
ListBox_Priorities_Today_Sell.Text = xlBlatt2.Cells(row + 1, spalte + 5).value

lblGasart_Tomorrow_Buy.Text = xlBlatt2.Cells(row + 2, spalte + 1).value
lblMaxMenge_Tomorrow_Buy.Text = xlBlatt2.Cells(row + 2, spalte + 3).value
lblKey_Tomorrow_Buy.Text = xlBlatt2.Cells(row + 2, spalte + 4).value
ListBox_priorities_Tomorrow_Buy.Text = xlBlatt2.Cells(row + 2, spalte + 5).value

lblGasart_Tomorrow_Sell.Text = xlBlatt2.Cells(row + 3, spalte + 1).value
lblMaxMenge_Tomorrow_Sell.Text = xlBlatt2.Cells(row + 3, spalte + 3).value
lblKey_Tomorrow_Sell.Text = xlBlatt2.Cells(row + 3, spalte + 4).value
ListBox_priorities_Tomorrow_Sell.Text = xlBlatt2.Cells(row + 3, spalte + 5).value
Else : row = row + 1
End If
End If
End While

If found = False Then
MsgBox("nichts gefunden")
End If
Reply With Quote
Reply

Tags
findmethod, for each, nullreferenceexception


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
Read out values from a specific row
Read out values from a specific row
Read out values from a specific row Read out values from a specific row
Read out values from a specific row
Read out values from a specific row
Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row Read out values from a specific row
Read out values from a specific row
Read out values from a specific row
 
Read out values from a specific row
Read out values from a specific row
 
-->