There is an excel document in which floating-point numbers are stored in the cells. I try to count them, but I get the error "Type mismatch":

"Run-time error '13': Type mismatch".

For example, in the cells the following data:

 A1: 1,00 B1: 25,17 A2: 3,00 B2: 133.67

I'm trying to count these numbers like this:

 Dim Count As Double Dim Cost As Double Dim Summ As Double For i = 1 To LastRowOpenDbfSheet (Цикл с первой до последней строки в документе) Count = sheetOpenDbf.Cells(i, 12) // Так не работает Count = sheetOpenDbf.Cells(i, 12).Value // Так не работает Count = CDbl(sheetOpenDbf.Cells(i, 12)) // Так не работает

Then these numbers Count, Cost need to be multiplied. What is the correct data type to specify and how to read the values ​​in the cells so that it would work and you could then multiply the numbers?

  • StrCount, StrCost - where is it in your description? Set - assignment to an object variable. Why do you need it? In В2 value with a dot - is it? - vikttur
  • @vikttur corrected the description, in a hurry just wrote, I apologize. In B2 value with a dot. In general, the value with a dot can be anywhere. - Sanvirtus

2 answers 2

A “number” with a dot is a text, with mathematical operations with such data there will be an error. It is necessary to bring the data to a single type.

 Sub Sum_() Dim aData() Dim dSum As Double Dim i As Long With ActiveSheet ' если нужно, указать другой лист' i = .Cells(.Rows.count, 1).End(xlUp).Row aData = .Range("A1:B" & i).Value For i = 1 To UBound(aData) aData(i, 1) = Replace(aData(i, 1), ".", ",") aData(i, 2) = Replace(aData(i, 2), ".", ",") dSum = dSum + aData(i, 1) * aData(i, 2) Next i .Cells(1, 3).Value = dSum End With End Sub

Correctly store data in the same format.

Select data range, Ctrl+H , FIND a full stop, REPLACE with a comma, OK .

With normal data and macros are not needed:

 =СУММПРОИЗВ(A1:A100;B1:B100)

    When working with basic types (numbers, strings, etc.), i.e. when assigning values, set is not used. Set is needed to assign an identifier to a class object. Those. in your case will be:

     Dim Count As Double Dim Cost As Double Dim Summ As Double Dim i as integer For i = 1 To LastRowOpenDbfSheet Count = sheetOpenDbf.Cells(i, 12) Count = sheetOpenDbf.Cells(i, 12).Value Count = CDbl(sheetOpenDbf.Cells(i, 12)) Next i