Member Avatar for tgifgemini

Hello everybody,
my program have been working and today I'm getting run time error 1004 - invalid number of arguments on this line of code:

mystr = "Comments:" & "'" & xlApp.Clean(Trim(rsin![Comments]))

Also, I am getting a "Runtime error 1004 - unable to set the RowHeight Property of the range class" on this code:

RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 1).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) + newlinecnt * .Font.Size

Can anyone tell me what went wrong?

Below is my entire procedure:

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]
       'xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsin![Comments]))
          
        mystr = "Comments:" & "'" & 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)
                   newlinecnt = newlinecnt + 2
                   Pos = Pos + 2
               End If
            End If
        Loop While Not Pos = 0
        xlWksht.Cells(ii + 1, 1).Value = mystr
         
        'xlWksht.Range("A12").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) + newlinecnt * .Font.Size
       End With
       
       rsin.MoveNext
    Loop

Thanks.
tgifgemini

  • 1 Contributor
  • 1 Reply
  • 520 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by tgifgemini
Member Avatar for tgifgemini

Problem solved.
The "Comments" field in my input table had null values, so I fixed it with this code:

If rsin![Comments] = "" Or IsNull(rsin![Comments]) Then
     mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
 Else
     mystr = "Comments:" & "'" & xlApp.Clean(Trim(rsin![Comments]))
 End If

Thanks.
tgif

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.