Issue in Range and If statement . Please help

[vop2311]

Hi,

I am 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

 

[combo]

In this way you can pick a value of single cell range. Depending on your needs you can use Value2 instead, that returns 2D array, or loop through cells (ranges) in Rng1 and Rng2.
I would also check the required type of thecountX variable. Due to data type limitations for integers, formula result is truncated to full number, above 32767 an overflow error is reported.

combo

 

[mintjulep]

Set Rng1 = range("C1:C25") --> 25 cells --> 25 values

Set Rng2 = range("B1:B25") --> 25 cells --> 25 values


If Rng1.Value >= 0 And Rng2.Value <= 999999 Then --> of the 50 values in the two ranges, which 2 might be being used for this calculation?

 

[SkipVought]

Please explain exactly WHAT you are doing.
In your code example, you reference rows 8, 9, 10 & 13??????

What relevance do rows 1:7, 11:12, 14:25 have (are you assign 1:25 in your two ranges in columns B & C?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and this is not consistent either???

Basically I want to calculate formula only if values are [highlight #E9B96E]greater than[/highlight] 0 and [highlight #8AE234]less than[/highlight] [highlight #FCE94F]099999[/highlight].

If Rng1.Value[highlight #E9B96E] >=[/highlight] 0 And Rng2.Value [highlight #8AE234]<=[/highlight] [b][highlight #FCE94F]999999[/highlight][/b] Then

...

MsgBox "Give value [highlight #E9B96E]between[/highlight] 0 and [b][highlight #FCE94F]999999999999[/highlight][/b]"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vop2311]

That is just a comment so please ignore.

Someone told me to use like this but i am not sure how would it work as I am new to VBA . Please help.

Thanks,
Vik

WorksheetFunction.Sum (Rng1)
WorksheetFunction.Sum (Rng2)

 

[SkipVought]

Please explain your overall objective, without trying to use code and formulas.

Just explain in prose, please. Then we can help you find an appropriate solution

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vop2311]

ok.

My logic is that I want to compute some formula based on condition of some cells range as below.

if cell range value of c1 to c25 and b1 to b25 is greater than 0 and then less than 999999 do

(


(c10*c8)-(c9*b13)/(C10+B13)


)


otherwise

display "value should be between 0 to 999999"

end.

 

[SkipVought]

'
    Dim Rng1 As Range, Rng2 As Range, thecountX

    Set Rng1 = Range("C1:C25")
    Set Rng2 = Range("B1:B25")
    
    If (WorksheetFunction.CountIf(Rng1, "<0") + WorksheetFunction.CountIf(Rng2, ">999999")) = 0 Then
        
        'Here We are calculating value of X and storing in varilable "thecountX".
        thecountX = (Range("C10") * Range("C8") - Range("C9") * Range("B13")) / [highlight #FCE94F]([/highlight]Range("C10") + Range("B13")[highlight #FCE94F])[/highlight]
        
    Else
        MsgBox "Give value between 0 and 999999999999"
    End If

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vop2311]

Thanks SkipVought.

Truly appreciated. It worked well