run-time error 13: type mismatch in VBA (Excel 2007)

[zeno]

Hello,
I'm writing a VBA program and receive "run-time error 13: type mismatch"

In the code below the line in bold receives the error.

<code>   Let FactornEMA(nEMA) = 2 / (FactorMA(nEMA) + 1)   'run-time error 13: type mismatch
  Let Cells(1 + nData, colEMA(nEMA)) = FactornEMA(nEMA) * (Cells(1 + nData, 4) - Cells(nData, 4))                                </code><code>  </code><code>Else       
  End If</code>

For clarification, FactorMA(nEMA) is an array of about 2 possible values a and b.

I have all parts of the line defined as variables and I use that calculation as a constant in the next line.
Can somebody tell me why I receive this error?
Thanks.

 

[tigeravatar]

zeno,

FactorMA(nEMA) is an array of about 2 possible values a and b

If FactorMA(nEMA) can only be "a" or "b", then you cannot use them to perform mathematical operations, such as addition and division.

This will give a Runtime error as you've described. Here's an example that will also result in the type mismatch. This happens because you cannot add to a non-numeric value:

Sub tgr()
    
    MsgBox "a" + 1
    
End Sub

 

[zeno]

Thank you for your response.
What is your suggestion to solve this?
I have to make this calculation. So I can calculate this constant first and then use it in the next line for another calculation.
Or I can calculate that constant for every row and so make one single, bigger calculation?

 

[tigeravatar]

What, exactly, is the calculation you are trying to make with letters? You need to use numbers to perform calculations...

2/(a+1) won't work, but 2/(1+1) will.

 

[zeno]

I put more code to provide better context.
the constant 'FactorMA(nEMA)' can only have 2 values. I thought that calculating that constant first might make the calculation in the next line easier. Maybe it's better to only make 1 single calculation instead of 2 separate ones?

<code>Dim FastPeriod, SlowPeriod, MACDPeriod, FactorMA, FactornEMA   Dim nEMA Dim colEMA  For nEMA = 1 To 2      Cells(1, colEMA(nEMA)) = "EMA-" & FactorMA(nEMA)     Cells(1, colEMA(3)) = "EMA" & FactorMA(2) & "-EMA" & FactorMA(1)      For nData = 1 To Data         DataCount = DataCount + 1                        If DataCount >= FactorMA(nEMA) Then             If DataCount > FactorMA(nEMA) Then                                               Else             End If                        Let FactornEMA(nEMA) = 2 / (FactorMA(nEMA) + 1)   'run-time error 13: type mismatch                        Let Cells(1 + nData, colEMA(nEMA)) = FactornEMA(nEMA) * (Cells(1 + nData, 4) - Cells(nData, 4))                        Else         End If     Next nData

The view of this code displays in only 1 line. I have not written it that way though.
</code>

 

[zeno]

I have resolved the error-message.
Thank you for your help!