CODEWITHSUNDEEP
vb.netexcel
user2536008
user2536008

Reputation: 215

Display the textboxes results in Excel sheet cells in vb.net

I have two textboxes on a form; one hold the word and the other one holds its numeric value that is calculated based on what the user types. I have a sub that separates each sentence and displays the total for each sentence in Excel. This is the output that being generated from the vb.net code which is listed below. All the words are in A column and all the values in B column. How do I modify the code so it inserts each sentence and its value in two columns? After it reaches the string "Total for sentence 1." it should start inserting the words and its values in columns C & D and so forth. Sentence one and its value should be displayed in columns A & B, sentence two and its value should be displayed in columns C & D and so on. Please see the attached image. Text in red is what I currently have and below it is the desired result. Thanks!enter image description here

My Code in vb.net is:

Private Sub Export_Click(sender As System.Object, e As System.EventArgs) Handles btnCreate.Click

    xlWorkBook = xlApp.Workbooks.Add

    xlApp.Visible = True

    xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    Dim columnANum As Integer
    Dim columnBNum
    columnBNum = 2
    columnANum = 2
    With xlWorkSheet
        .Range("A1").Value = "Word"
        For Each cellA As String In txtWord.Text.Split(vbLf)

            .Range("A" & columnANum.ToString).Value = cellA
            columnANum += 1
        Next
        .Range("B1").Value = "Value"
        For Each cellB As String In txtValue.Text.Split(vbLf)
            .Range("B" & columnBNum.ToString).Value = Convert.ToInt32(cellB)
            columnBNum += 1
        Next


    End With
End Sub

Upvotes: 0

Views: 5862

Answers (2)

D_Bester
D_Bester

Reputation: 5911

EDIT: I made significant changes to this code and tested it; it should do what you're looking for

Sub testExcelColumns()
    Dim xlApp As Object = CreateObject("Excel.Application")
    Dim xlWorkBook As Object
    Dim xlWorkSheet As Object

    xlWorkBook = xlApp.Workbooks.Add
    xlApp.Visible = True

    xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    Dim RowNum As Integer = 1
    Dim ColNum As Integer = 1

    'use .cells(row, column) instead of .range
    xlWorkSheet.cells(RowNum, ColNum).Value = "Word"

    For Each cellA As String In txtWord.Text.Split(vbLf)

        'increment the row
        RowNum += 1

        'set the value
        xlWorkSheet.cells(RowNum, ColNum).value = cellA

        If cellA.StartsWith("Total") Then
            ColNum += 2
            RowNum = 1
            xlWorkSheet.cells(RowNum, ColNum).Value = "Word"
        End If

    Next

    'increment to the second column and first row
    ColNum = 2
    RowNum = 1

    'set title
    xlWorkSheet.cells(RowNum, ColNum).Value = "Value"

    For Each cellB As String In txtValue.Text.Split(vbLf)

        'increment the row
        RowNum += 1

        'set the value
        xlWorkSheet.cells(RowNum, ColNum).value = cellB

        'get value of cell to the left
        Dim t As String = xlWorkSheet.cells(RowNum, ColNum).offset(0, -1).value
        If Not t Is Nothing AndAlso t.StartsWith("Total") Then
            ColNum += 2
            RowNum = 1
            xlWorkSheet.cells(RowNum, ColNum).Value = "Value"
        End If

    Next

End Sub

Upvotes: 1

D_Bester
D_Bester

Reputation: 5911

Try using 

Dim RowNum as Integer = 1
Dim ColNum as Integer = 1

use .cells instead of .range

xlWorkSheet.Cells(RowNum, ColNum).value = "Word"
xlWorkSheet.Cells(RowNum, ColNum).value = "Value"

update the row and column as needed

RowNum += 1
ColNum += 1

Upvotes: 0

Related Questions