Macro programming run time error 13

[Nigel]

I have been developing an appliaction using visual basic in a macro. When
compiling the code I get a run time error in the IF Then Else section. It is
a type mismatch error and occurs in the line immediately after the 'Else'.
It does not seem to ocurr in the line after the IF statement although both
lines of code are very similar. Does anyone know why this is happening? I
have tried lots of variations to make it work, but without success. Here is
the full code:
Dim varA As Variant, varB As Variant, varC As Variant, rngProb As Variant
Dim ratioA(500) As Variant, ratioB(500) As Variant, ratioC(500) As Variant,
rngValue(1000, 500) As Variant
Dim n As Integer
Set varA = Worksheets(1).Range("D5505")
Set varB = Worksheets(1).Range("E5:E505")
Set varC = Worksheets(1).Range("F5:F505")
Set rngProb = Worksheets(1).Range("C5:C505")
'varA, varB and varC are the columns containing the min, most likley and max
costs in the first sheet (called "register")
'rngProb is the probability associated with the each risk in the register
contained in the first worksheet
n = Range("E2")
' n is the number of risks in the register. Cell E2 contains this
information based on the COUNT formula
Dim rngRandom(1000, 500) As Variant, rngXoverB(1000, 500) As Variant
For i = 1 To n
ratioA(i) = varA(i) / varB(i)
ratioB(i) = varB(i) / varB(i)
ratioC(i) = varC(i) / varB(i)
Next i
'the above for-next loop is the calculation for normalised costs for each
risk in the register
For i = 2 To (n + 1)
Cells(5, i) = ratioA(i - 1)
Cells(6, i) = ratioB(i - 1)
Cells(7, i) = ratioC(i - 1)
'fills the spreadsheet in rows 5 to 7 with the normalised cost min, most
likely, max estimates
Next i
'
For i = 2 To (n + 1)
For j = 8 To 1008
Cells(j, i) = "=RAND()"
'fills the spreadsheet with 1000 random numbers for each risk of the regsiter
'rng Random is an array of these random numbers which is used in subsequent
calculations
'this random number represents the area under the probability density
function and
'is the probability of the cost (rngXoverB) arising for the risk (after
multiplying by the normalising factor varB
'and the probability of the risk)
rngRandom(j, i) = Cells(j, i)
If rngRandom(j, i) < (ratioB(i - 1) - ratioA(i - 1)) / (ratioC(i - 1) -
ratioA(i - 1)) Then
rngXoverB(j, i) = rngRandom(j, i) +
("=SQRT(rngRandom(j,i)*(ratioB(i-1)-ratioA(i-1))*(ratioC(i-1)-ratioA(i-1)))")
Else
rngXoverB(j, i) = ratioC(i - 1) -
("=SQRT((1-rngRandom(j,i))*(ratioC(i-1)-ratioB(i-1))*(ratioC(i-1)-ratioA(i-1)))")
End If
rngValue(j, i) = rngXoverB(j, i) * varB(i - 1) * rngProb(i - 1)
Cells(j + 2020, i) = rngValue(j, i)
Next j
Next i

 

[Nigel]

I forgot to mention that I use EXCEL 2003

 

[TheCrash]

I think that some variable is incorrect type, because "Error 13" is type
mismatch and you make math operation so if the variable is other type than
integer, double, currency you can see this error.

 

[Nigel]

I discovered through discussion with colleagues that this error is caused by
the fact that Visual Basic int he macro does not support the function SQRT.
The means of overcoming this difficulty is simply to use the exponent feature
and raise the expression to the power of 0.5 eg (expression)^0.5