-
February 8th, 2013, 12:55 AM
#1
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??
-
February 8th, 2013, 01:35 AM
#2
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.
-
February 8th, 2013, 02:09 AM
#3
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
-
February 8th, 2013, 08:10 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|