VB 6.0 - Excel spreadsheet - split a record so that it writes on two separate rows

[Giftx]

Hi everyone,
I have an on-going display problem in another thread("#######") and I thought of a way to solve this problem and eliminate all the headaches:
Looking at the code below, you'll notice that all the field headers are on the same row: example: Firstname Lastname Salary Comments

What if I split the record so that firstname, lastname Salary are written on the same line(row) and the comments is written on the next line(row) by itself and then I can merge the comment row with other cells so there's enough room for all the data to display?

This is how I want the records to display:

Firstname Lastname Salary
Jack John 5,000.00
Comments:
"This employee is over 50 years old and have put in 25 years in this organization"
etc.

Do you understand where I'm going with this?

What is the code I can use to accomplish the above format.
Below is the module that writes the records on the spreadsheet:

ii = 6 
Do Until rsin.EOF = True
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii, 9).Value = "'" & rsin![Comments]
       rsin.MoveNext
Loop

Thanks.
GiftX

[Giftx]

Hi everyone,
I have an on-going display problem in another thread("#######") and I thought of a way to solve this problem and eliminate all the headaches:
Looking at the code below, you'll notice that all the field headers are on the same row: example: Firstname Lastname Salary Comments

What if I split the record so that firstname, lastname Salary are written on the same line(row) and the comments is written on the next line(row) by itself and then I can merge the comment row with other cells so there's enough room for all the data to display?

This is how I want the records to display:

Firstname Lastname Salary
Jack John 5,000.00
Comments:
"This employee is over 50 years old and have put in 25 years in this organization"
etc.

Do you understand where I'm going with this?

What is the code I can use to accomplish the above format.
Below is the module that writes the records on the spreadsheet:

Code:
ii = 6 
Do Until rsin.EOF = True
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii, 9).Value = "'" & rsin![Comments] <=== This to display on the next line (Row)
       rsin.MoveNext
Loop

Thanks.
GiftX

[westconn1]

Do Until rsin.EOF = True
       ii = ii + 2
       xlWksht.Cells(ii, 1).value = rsin![Req No]
       xlWksht.Cells(ii, 2).value = rsin![Description]
       xlWksht.Cells(ii, 3).value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii + 1, 1).value = "'" & rsin![Comments]
       rsin.MoveNext
Loop

this will put the comment on the next line for each record, you may need to change the initial value of ii form 6 to 5

if you want to put headings for each record, then each record will take four lines instead of 2, but is not hard to do

[Giftx]

Hi westconn1,
okay, that objective is been acheived, but the spreadsheet display looks screwy: The first batch of data writes on the first line(row) and the comments field writes on the next line(row)..... good.
Display problem: the comment line is written on column A. because the data in that column is huge(Length of over 2000) the row height is excessively too much. my suggestion is to cells merge so that the data can spread accross to other unused cells on that row.
I am using this code:

xlWksht.Range("A2:K2").MergeCells = True

to merge some cells to allow the long text in that row to display properly.
How can do the same for all the "comments" line?
I do appreciate your help.
GiftX.

[westconn1]

don't have time to try now, but you can try increasing the width of individual cells that contain comment