Sumproduct Worksheetfunction producing "run time error 13" type mismatch

0
votes

I try to use the following sumproduct function in my macro:

=SUMPRODUCT(--(LEN('Data Weekly Classic'!B:B)>1))-1

The data sheet, as well as the column to search changes, so i am using variables.

My code:

Public commentsheet As Worksheet
Public commentcolumn As String
Public commentcell As String
Public rownumber As Integer

Private Sub Workbook_Open()
Set commentsheet = ThisWorkbook.Sheets("Data Weekly Classic")
commentcolumn = Application.WorksheetFunction.VLookup(Sheet4.Range("A2").Value, Sheet8.Range("P1:Q8"), 2, 0)
rownumber = 2
commentcell = commentcolumn & rownumber
End Sub

Sub broken()
Dim commentstotalrange As String
commentstotalrange = commentcolumn & ":" & commentcolumn
Sheet4.Range("D13").Value = Application.WorksheetFunction.SumProduct(--(WorksheetFunction.Len(commentsheet.Range(commentstotalrange) > 1)) - 1)
End Sub

Any idea why i get a error 13: type mismatch for this line?:

Sheet4.Range("D13").Value = Application.WorksheetFunction.SumProduct(--(WorksheetFunction.Len(commentsheet.Range(commentstotalrange) > 1)) - 1)

First I wrote it as:

Sheet4.Range("D13").Value = Application.WorksheetFunction.SumProduct(--(Len(commentsheet.Range(commentstotalrange) > 1)) - 1)

without the worksheetfunction before "LEN". It produced the same error. I added the worksheetfunction before "LEN" because I thought, maybe I have to tell VBA that LEN is also the excel LEN function. The error did not change though.

1
vbaexcel
You could try using Evaluate or [...] instead of Application.WorksheetFunction ? - Patrick Honorez

1 Answers

1
votes

You could use:

Sheet4.Range("D13").Value = Application.WorksheetFunction.Countif(commentsheet.Range(commentstotalrange), "??*") - 1

if the cells contain text.