Type Mismatch Error When calling Sub

[Aspiring_Excel_Master]

Hi


I have a procedure that runs fine when I run it standalone but when I call it from another sub it generates a Type Mismatch Error (run time error 13)




For i = 4 To ColNum
Cells(8, i).Value = Cells(4, i) / Cells(5, i)
'calculate standardized
Sheets("Probit").Cells(11, i).Value = Cells(4, i) * Application.StDev(Range(Cells(15, i), (Cells(LastRow, i)))) / WorksheetFunction.StDev(Range(Cells(15, 2), (Cells(LastRow, 2))))


The first line "(Cells(8, i).Value = Cells(4, i) / Cells(5, i)) "populates fine but the next section with the standard deviation calculation generates the error. i.e debugging takes me to this line:


Sheets("Probit").Cells(11, i).Value = Cells(4, i) * Application.StDev(Range(Cells(15, i), (Cells(LastRow, i)))) / WorksheetFunction.StDev(Range(Cells(15, 2), (Cells(LastRow, 2))))



I've tried different things but cannot get it to run without an error when calling the sub that this bit of code sits in. Any ideas for fixes?

This is how the variables are defined
Dim LastRow As Long, i As Long
Dim ColNum As Integer, obs As Integer, outvariables As Integer



Thanks

AEM

 

[ranman256]

WHERE is the error? what line?

 

[Fluff]

Are all those cells on that line on the same sheet?

 

[Aspiring_Excel_Master]

Hi

Yes all of the code relates to the same sheet. The error occurs at this line only: The proceeding line runs/calculates fine:

Sheets("Probit").Cells(11, i).Value = Cells(4, i) * Application.StDev(Range(Cells(15, i), (Cells(LastRow, i)))) / Application.StDev(Range(Cells(15, 2), (Cells(LastRow, 2))))

 

[Fluff]

I suspect that the "Probit" is not the active sheet & you also seem to have some extra brackets, try

With Sheets("Probit")
   .Cells(11, i).Value = .Cells(4, i) * Application.StDev(.Range(.Cells(15, i), .Cells(LastRow, i))) / Application.StDev(.Range(.Cells(15, 2), .Cells(LastRow, 2)))
End With

 

[Aspiring_Excel_Master]

Thanks Fluf.

I tried your code but still get the same error and can confirm the sheet name is correct.

As I mentioned, when I run the sub by itself instead of calling it from another procedure it calculates fine without any errors so the formula/sheet references etc does not appear to be the issue.

Also right before this sub is called there is another sub that gets called. To test I removed the other sub call - and now the call to this sub now works fine without error. So it looks like there is some conflict between the two subs that get called in the procedure. I have no idea why. They both reference the same sheet but do slightly different things!

AEM

 

[Fluff]

Check that none of the cells that line is looking at when you get the error contain formulaic errors such as #N/A #VALUE etc