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