Question Loop to test if cell has anything in it?

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
I want to use a loop to check to see if there is any data in the first column of a row, if there is data I want it to go to the next row and check again, once it finds an empty row, I want it to right data from an array I have created. I understand the concepts of loops and incrementing to make it go down one more row, but I cannot figure out how to check the particular row in the first column to see if it is empty or not. Can anyone help me with this If Else statement, how should I be evaluating this?
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
I forgot to mention, that the rows and columns are coming from an Excel spreadsheet
 

22-degrees

Well-known member
Joined
Feb 11, 2012
Messages
156
Location
South East Ireland
Programming Experience
1-3
Edit: was eating and writing so took a while to generate my reply during which time you posted clarification on rows and columns.. Post what code you already have and I can assist you. I have worked plenty with excel.



Assuming by "row" and "column" you are referring to a DataGridView and you want to check if a particular "Cell" in the row has no data in it, it is the same process as when you want to add data to the cell manually.

For example, if you want to check if the 1st cell in the 5th row has data in it or not and add data from your array to that cell if it's blank then:

If DataGridView1.Rows(4).Cells(0).Value = Nothing Then
    DataGridView1.Rows(4).Cells(0).Value = myArrayData(0)
End If


Good Luck
 
Last edited:

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
Thanks, however I have never used Data Grids, so when I put the code from above, it says DataGridView1 is not declared.
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
Imports Microsoft.Office.Interop
Imports System.Data

Public Class Form1

Dim objApp = New Excel.Application()
Dim objBooks = objApp.Workbooks
Dim objBook = objBooks.Add
Dim objSheets = objBook.Worksheets
Dim objSheet = objSheets(1)
Dim range As Excel.Range
Dim testRange As Excel.Range
Dim myValues(4) As String

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Submit.Click
myValues(0) = FName.Text
myValues(1) = LName.Text
myValues(2) = GCNum.Text
myValues(3) = PinNum.Text
myValues(4) = AmtNum.Text

Dim intRow As Integer
Dim intCol As Integer
Dim finished As Integer = 1

intRow = 1
intCol = 1

'this is where I need the loop and the if statement to test if the cell is empty or not.

objApp.Visible = True
objApp.UserControl = True

range = Nothing
objSheet = Nothing
objSheets = Nothing
objBooks = Nothing
End Sub
End Class

This is what I have so far, pretty basic I think, just not sure about declaring datagridview1, the code you gave me above looks perfect for what I want to do, just need to know how to declare datagridview1
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
Do While endLoop = False
If DataGridView1.Rows(intRow).Cells(intCol).Value = Nothing Then
For i = 0 To 4
DataGridView1.Rows(intRow).Cells(intCol).Value = myValues(i)
Next
endLoop = True
Else
intRow = intRow + 1
endLoop = False

End If
Loop

I added this loop in and put in a DataGrid in design mode so I do not get the declaration error, but now I get this error:
An unhandled exception of type 'System.ArgumentOutOfRangeException' occurred in mscorlib.dll
and it highlights my If DataGridView1.Row... line of code?
 

22-degrees

Well-known member
Joined
Feb 11, 2012
Messages
156
Location
South East Ireland
Programming Experience
1-3
What exactly is it that you want to do? Do you want to add a row of data underneath existing rows? or will there sometimes be blank rows in the middle of the DataGridView that you want to fill with data?

Also, when you have the data transferred, what is it you want to do then? I mean, what happens to the data when you close the program? If I have a better idea of the bigger picture then I can better guide you in your approach.
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
There is an excel file with 5 columns of values in each row, I want this to find the last row of data, go beneath it by one row and enter the data I have saved in the array. There should not be blank rows in the middle, after the data is transferred I want to save the excel file.
 

22-degrees

Well-known member
Joined
Feb 11, 2012
Messages
156
Location
South East Ireland
Programming Experience
1-3
Sorry about the delay, Mrs is going out of town today so I was distracted.

Here is what you need i believe

Imports Microsoft.Office.Interop

Public Class Form1

   Dim xlApp As Excel.Application
   Dim xlWB As Excel.Workbook
   Dim xlSheet As Excel.Worksheet
   Dim xlCell As Excel.Range

   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   Dim col As Integer
   Dim lastRow As Excel.Range

   Dim myValues(4) As String

   
   Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

      oExcel = CreateObject("Excel.Application")

      oBook = oExcel.Workbooks.Open("C:\test.xlsx") ' Change this string to the exact address of the excel file

      oSheet = oBook.Worksheets(1)

      oExcel.visible = True

   End Sub

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Submit.Click

      col = 1

      myValues(0) = FName.Text
      myValues(1) = LName.Text
      myValues(2) = GCNum.Text
      myValues(3) = PinNum.Text
      myValues(4) = AmtNum.Text

     
      Try

         lastRow = oSheet.cells(oSheet.rows.count, col).End(Excel.XlDirection.xlUp)

         For i = 0 To 4

            oSheet.cells(lastRow.Row + 1, col).value = myValues(i)
            col += 1

         Next

         oBook.save()

      Catch ex As Exception
         MsgBox(ex.ToString)
      End Try


   End Sub


Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

      Try

         oBook.save()
         oBook.close()

      Catch ex As Exception
         MsgBox(ex.ToString)
      End Try

   End Sub


End Class


Hope it helps
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
That works great, now I just have one more question, I would like this to send the info to two different excel spreadsheets, is it possible to open 2 excel apps and have the one button send the info to two different spreadsheets? Or do I need to have another button to send the info to the other excel spreadsheet?
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
This is what I have done so far, I used two buttons, it seems to work, the only funny thing is that when I click the Submit1 button, it asks if I want to replace the text.xlsx file

Imports Microsoft.Office.Interop

Public Class Form1

Dim xlApp As Excel.Application
Dim xlApp1 As Excel.Application

Dim xlWB As Excel.Workbook
Dim xlWB1 As Excel.Workbook

Dim xlSheet As Excel.Worksheet
Dim xlsheet1 As Excel.Worksheet

Dim xlCell As Excel.Range
Dim xlCell1 As Excel.Range

Dim oExcel As Object
Dim oExcel1 As Object

Dim oBook As Object
Dim oBook1 As Object
Dim oSheet As Object
Dim oSheet1 As Object

Dim col As Integer
Dim lastRow As Excel.Range

Dim col1 As Integer
Dim lastRow1 As Excel.Range

Dim myValues(4) As String
Dim myValues2(4) As String

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Open("C:\test.xlsx") ' Change this string to the exact address of the excel file

oExcel1 = CreateObject("Excel.Application")
oBook1 = oExcel1.Workbooks.Open("C:\TestExcel.xlsx") ' Change this string to the exact address of the excel file

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Submit.Click

oSheet = oBook.Worksheets(1)
oExcel.visible = True

col = 1

myValues(0) = FName.Text
myValues(1) = LName.Text
myValues(2) = GCNum.Text
myValues(3) = PinNum.Text
myValues(4) = AmtNum.Text

Try

lastRow = oSheet.cells(oSheet.rows.count, col).End(Excel.XlDirection.xlUp)

For i = 0 To 4

oSheet.cells(lastRow.Row + 1, col).value = myValues(i)
col += 1

Next

oBook.save()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

End Sub

Private Sub Submit2_Click(sender As Object, e As EventArgs) Handles Submit2.Click

col1 = 1

oSheet1 = oBook1.Worksheets(1)
oExcel1.visible = True

myValues2(0) = FName.Text
myValues2(1) = LName.Text
myValues2(2) = GCNum.Text
myValues2(3) = PinNum.Text
myValues2(4) = AmtNum.Text

Try
lastRow1 = oSheet1.cells(oSheet1.rows.count, col1).end(Excel.XlDirection.xlUp)
For i = 0 To 4

oSheet1.cells(lastRow1.Row + 1, col1).value = myValues2(i)
col1 += 1

Next

oBook1.save()

Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles User_Exit.Click
oBook.close()
oBook1.close()
Me.Close()
End Sub
End Class
 

22-degrees

Well-known member
Joined
Feb 11, 2012
Messages
156
Location
South East Ireland
Programming Experience
1-3
There is something you are not telling me.. I pasted your code and all works just fine for me. While there is no "Submit1" button, I am going to assume you are referring to "Submit" button.

Also, you didn't need to double up on everything especially what is not being used.. For starters, get rid of the following.. I pasted it from an old project but you are not using it.

 Dim xlApp As Excel.Application
   Dim xlApp1 As Excel.Application

   Dim xlWB As Excel.Workbook
   Dim xlWB1 As Excel.Workbook

   Dim xlSheet As Excel.Worksheet
   Dim xlsheet1 As Excel.Worksheet

   Dim xlCell As Excel.Range
   Dim xlCell1 As Excel.Range



What exactly is it you are trying to do? Do you want the same data saved to 2 files or do you want sometimes the data saved to one file and sometimes to the other file?
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
I want all of the data saved to the first excel file, and then I am going to have only some of the data saved to the other excel file, also, would it be possible to populate a web page form that requires all of the data as well?
 

22-degrees

Well-known member
Joined
Feb 11, 2012
Messages
156
Location
South East Ireland
Programming Experience
1-3
I have no experience with putting stuff onto the web with vb but I imagine it is no different than finding the browser window and posting messages to it. What exactly are you doing? what is the project?
 

awooster

Member
Joined
Nov 23, 2012
Messages
11
Programming Experience
5-10
I have to input this data into two different excel files and a website, just trying to make it so I only have to enter the information once and have it populate all three. But even just putting the info into this vb app and having it populate the two excel files will definitely help out. so what I have right now will be an improvement.
 
Top Bottom