I
iereinoso
I am writing a program in VB.NET that populates an Excel spreadsheet.
I know that it is not possible to have autoheight or autofit on merged
cells. I need the height of the row to expand to fit an undetermined
amount of text.
I found some code previously posted and changed the code to fit my
program. Now, I get an error on this line of code (full code below):
For Each CurrCell In CoverWs.Range(strA1).MergeArea
I believe the error is in the CurrCell as Excel.Range, but I'm not
sure.
And advice would be appreciated,
Thanks,
Elena
Error:
An unhandled exception of type
'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll
Additional information: Member not found.
Current Code:
Dim CurrentRowHeight As Single, MergedCellRgWidth As
Single
Dim CurrCell As Excel.Range
Dim ActiveCellWidth As Single, PossNewRowHeight As
Single
If CoverWs.Range(strA1).MergeCells Then
With CoverWs.Range(strA1).MergeArea
If .Rows.Count = 1 And .WrapText = True Then
ThisApplication.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth =
CoverWs.Range(strA1).ColumnWidth
For Each CurrCell In
CoverWs.Range(strA1).MergeArea
MergedCellRgWidth =
CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit()
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight >
PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
I know that it is not possible to have autoheight or autofit on merged
cells. I need the height of the row to expand to fit an undetermined
amount of text.
I found some code previously posted and changed the code to fit my
program. Now, I get an error on this line of code (full code below):
For Each CurrCell In CoverWs.Range(strA1).MergeArea
I believe the error is in the CurrCell as Excel.Range, but I'm not
sure.
And advice would be appreciated,
Thanks,
Elena
Error:
An unhandled exception of type
'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll
Additional information: Member not found.
Current Code:
Dim CurrentRowHeight As Single, MergedCellRgWidth As
Single
Dim CurrCell As Excel.Range
Dim ActiveCellWidth As Single, PossNewRowHeight As
Single
If CoverWs.Range(strA1).MergeCells Then
With CoverWs.Range(strA1).MergeArea
If .Rows.Count = 1 And .WrapText = True Then
ThisApplication.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth =
CoverWs.Range(strA1).ColumnWidth
For Each CurrCell In
CoverWs.Range(strA1).MergeArea
MergedCellRgWidth =
CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit()
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight >
PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If