CODEWITHSUNDEEP
excelexcel-formulavba
Gaus Shaikh
Gaus Shaikh

Reputation: 209

Type mismatch error in excel formula

I am getting an error as type mismatch on last line.

If I put the number eg: 2080 in place of rowcount in formula, it works fine.

Dim count as integer
Dim rowcount as integer
Set rngData = ws1.Range("A1").CurrentRegion
rowCount = rngData.Rows.count

count = ws1.Evaluate("=SUM(IFERROR(1/COUNTIF(L2:L & rowCount, L2:L & rowCount,0))")

Upvotes: 0

Views: 303

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You need to seperate the Text and constant past of your Formula, with the variables part. You do that by splitting it with " & RowCount & ".

Note: you would be better to declare Dim rowcount as Long , also Dim count as Long.

If your formula worked with your code when instead of RowCount you had 2080, then change your formula to:

ws1.Evaluate("=SUM(IFERROR(1/COUNTIF(L2:L" & RowCount & ", L2:L" & RowCount & "),0))")

Upvotes: 2

Related Questions