Home » Forums » Open Source Learning Center » Question and Answer

Error while Range and IF statement. Please help

Submitted by vop2311 on 21 March, 2014 - 06:23

Hi,

I getting "error -13 type mismatch" in this line below from my code

"If Rng1.Value >= 0 And Rng2.Value <= 999999 Then"

Basically I want to calculate formula only if values are greater than 0 and less than 099999.

Any help would be appreciated.

Thanks,
Vik

'mycal() is function created by me to caluculate X.
Sub mycal()

Dim thecountX As Integer

Dim Rng1 As range, Rng2 As range
Set Rng1 = range("C1:C25")

Set Rng2 = range("B1:B25")

If Rng1.Value >= 0 And Rng2.Value <= 999999 Then

'Here We are calculating value of X and storing in varilable "thecountX".
thecountX = (range("C10") * range("C8") - range("C9") * range("B13")) / range("C10") + range("B13")

Else
MsgBox "Give value between 0 and 999999999999"
End If

'Here we are displaying the value of X from variable "thecountX".
MsgBox "the count is " & thecountX

'Endsub is used to end any function.
End Sub

‹ How to get weighted Average ? How is it possible to import more trt file in one Excel worksheet ? ›
»
Almir's picture

Set min and max value in the range

Submitted by Almir on 21 March, 2014 - 08:24.

It seems problem occurs when you check a single value against the range of cells. I am not sure, but maybe you need to set minimum and maximum values of ranges, instead of their values.

So, instead of:

"If Rng1.Value >= 0 And Rng2.Value <= 999999 Then"

try something like:

"If WorksheetFunction.Min(Rng1) >= 0 And WorksheetFunction.Max(Rng2) <= 999999 Then"

I guess that your condition checks every single values in two ranges. However, if you check total (sum) of each range, then you can replace Min and Max functions by Sum function.

If you need calculation for each row in the rng2 that satisfies conditions, then you need For Each...Next Loop. I guess this is not the case.

»

Error while Range and IF statement. Please help

Submitted by aidazzz on 4 June, 2014 - 13:02.

Hi Almir, Hi All,
I am havin similar problem (as previously posted),
Just slightly diferent "angle":

My formula reads, which works fine:

=+SUMIFS($I$15:$I$65535; $P$15:$P$65535;"<="&$P10)

I range - are numbers in cells
P range - being dates in cells
P10 - being date

However the present condition:
$P$15:$P$65535;"<="&$P10

I need to change into something that compares ranges (but not range against single cell only). I have tried following, which returns 0:
$P$15:$P$65535;"<="&$Q$15:$Q$65535

Any suggestions?

Many thanks!

»
Almir's picture

I think this should be an array formula

Submitted by Almir on 4 June, 2014 - 14:23.

Hi,
Try to enter formula as an array formula.

»
Vikas Verma's picture

Please share your workbook

Submitted by Vikas Verma on 21 March, 2014 - 06:34.

Hi Please share your workbook with dummy data

»