Converting Code from VBA to VB.NET

[Guest]

I have code that works in VBA that I must convert to VB.Net. It works
perfectly behind an Excel Form.

I am writing a program in VB.Net that fills an Excel Document. I've
exhausted my help in the Excel Programming Forum.

I was hoping there are some ideas here.

This is the VBA Code:
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
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
End Sub


I changed the code and now get an error on this line of code (full code
below): For Each CurrCell In CoverWs.Range(strA1).MergeArea

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



Any advice appreciated!

Thansk in advance,
Elena

 

[Andrew Taylor]

You don't show the definition and initialisation of strA1 in your
revised code. Maybe you just need to add the line:

strA1 = "A1"

 

[Guest]

It is in a loop. I apologize; I should have included the whole loop. It has
to be a string because the row is always changing. I hope this is clearer.
The code is below.

Thanks again,
Elena


If intCount1 > 0 Then
While intRow1 <> intCount1
Dim strRec =
dsRec.Tables(0).Rows(intRow1)("recommendations_content").ToString
Dim strA1 As String = "a" & intPrintRow
Dim strJ1 As String = "j" & intPrintRow
CoverWs.Range(strA1 & ":" & strJ1).Merge()
CoverWs.Range(strA1).Value = strRec
CoverWs.Range(strA1).Font.Bold = False
CoverWs.Range(strA1).WrapText = True

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

intPrintRow = intPrintRow + 1

intRow1 = intRow1 + 1



End While