split cell in excel n put it in two other cells
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: split cell in excel n put it in two other cells

Hybrid View

  1. #1
    Join Date
    Dec 2012
    Posts
    20

    split cell in excel n put it in two other cells

    i hav a problem.......i have an excel sheet which hav the B column values as
    B1=ant & boy
    B2=cat & doll etc.....
    i need to split these values and paste them in C & D columns in same row.....like i will have
    C1= ant, D1=boy
    C2=cat, D2=doll......ho wto achieve this??it may b simple.....but am having problems......any help??

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,830

    Re: split cell in excel n put it in two other cells

    I don't work in Excel so I can't help much but I can give you a hint.
    Have a look at the Split() function
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Dec 2012
    Posts
    20

    Re: split cell in excel n put it in two other cells

    i hav used split...n here is my code......
    Code:
    Dim xlapp As New Excel.Application
    Dim xlbook As New Excel.Workbook
    Dim xlsheet As New Excel.Worksheet
    Dim text As String
    Dim i As Integer
    Dim j As Integer
    Dim lastnonempty As Integer
    
    
    
    Private Sub Form_load()
    excel_value
    End Sub
    
    Public Sub excel_value()
    rowcnt = 2
    xlapp.workbooks.open ("D:\Baishali\try.xlsx")
    For i = 2 To 5
    text = xlsheet.Cells(i, 2)
    colcnt = 3
    cval = Split(text, "&")
    lastnonempty = -1
    For j = 0 To UBound(cval)
    If cval(j) <> "" Then
    lastnonempty = lastnonempty + 1
    cval(lastnonempty) = cval(j)
    End If
    texta = cval(j)
    xlsheet.Cells(rowcnt, colcnt) = texta
    colcnt = colcnt + 1
    Next
    rowcnt = rowcnt + 1
    Next
    End Sub
    anything wrong?am getting error 430 on red line

  4. #4
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,585

    Re: split cell in excel n put it in two other cells

    xlsheet is uninitialized; how should it know you mean to refer to a sheet (presumably the active one) in the workbook you just opened? Try this instead:

    Code:
    text = xlapp.ActiveSheet.Cells(i, 2)
    Of course you'll need to fix other relatedreferences later in the code as well.

    Or add this line before you make any reference to xlsheet:

    Code:
    Set xlsheet = xlapp.ActiveSheet
    That way you don't need to modify your references to xlsheet, unless they also contain other bugs.
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center