Hello everyone,
I am having a display problem within my spreadsheet. The data in the spreadsheet row is hidden. Looking at the spreadsheet, you will see that the row that contains the "comments" is hidden, but when you manually expand the cells, you see the data. I have a code within my module where I am doing some calculations to the display effect, but it's not working anymore. Please see the spreadsheet attachment to get an idea of what I am talking about.
Any insite will be greatly appreciated.
Thanks a million.
tgifgemini
Below is my working module:
ii = 5
w = 0
For Each R In xlWksht.Range("A8:h8"): w = w + R.ColumnWidth: Next
rht = xlWksht.Range("A6").RowHeight
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]
mystr = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsin![Comments]))
Do
Pos = InStr(Pos + 1, mystr, ":")
If Not Pos = 0 Then
If Mid(mystr, Pos - 5, 1) = "/" Then
mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
Pos = Pos + 2
End If
End If
Loop While Not Pos = 0
xlWksht.Cells(ii + 1, 1).Value = mystr
With xlWksht.Range(xlWksht.Cells(ii + 1, 1), xlWksht.Cells(ii + 1, 8))
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.IndentLevel = 0
.MergeCells = True
.RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 1).text) - Len("Comments:")) / w + rht + (rht - .Font.Size)
End With
rsin.MoveNext
Loop