Urgent help in excel macros

[Jayanthi]

I am having the minimum and maximum values in two columns in an exce
spread sheet.I need to check whether the given value falls between th
maximum and minimum values.

For e.g Minimum column contains value like AAA_0001 and Maximum colum
contains AAA_1000
Let the given value be AAA_560.I need to check whether this value i
between those two columns.
Could anyone help me out in solving this .....?


regards
Jayanth

 

[Roger Govier]

Hi

In a cell, say C1 enter your Minimum value AAA_0001
In D1 enter you rmax value AAA_1000

then the test is
=AND(A1>$C$1,A1<$D$1)

Your value of AAA_560.1 would return FALSE, but AAA_0560.1 would return
TRUE

 

[Jayanthi]

Thanks a lot Roger
I will try with this

Hi

In a cell, say C1 enter your Minimum value AAA_0001
In D1 enter you rmax value AAA_1000

then the test is
=AND(A1>$C$1,A1<$D$1)

Your value of AAA_560.1 would return FALSE, but AAA_0560.1 woul
return
TRUE


--
Regards

Roger Govier



Jayanthi <[email protected]
wrote:

 

[Tushar Mehta]

The answer depends on how you want to do the comparison. If you want
to do a alphanumeric comparison you may want to ensure that the lengths
of all strings are the same and follow Roger's suggestion.

If, on the other hand, the comparison is based on the numeric part of
the tokens, consider something like:

Suppose the min. value AAA_0001 is in C2 and the corresponding max.
value is in D2. Suppose your test value is in E2. Then, to check your
value is greater than the min. use =0+MID(E2,5,1024)>0+MID(C2,5,1024).
To check it is less than the max. use
=0+MID(E2,5,1024)<0+MID(D2,5,1024). Suppose the min. comparison result
is in F2 and the max. comparison result is in G2. Then, in H2 use the
AND() function to combine the two parts.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Don Guillett]

For archival purposes, please try to use more meaningful subject lines and
please refrain from using the word URGENT!. All requests here are equally
urgent.
Try a helper column (could hide) with the formula
=VALUE(MID(B6,FIND("_",B6)+1,LEN(B6)))
then use something like

Sub getnumberincellmax_min()
myval = 500
minval = Application.Min(Columns("c"))
maxval = Application.Max(Columns("c"))
If myval >= minval And myval <= maxval Then MsgBox "OK"
End Sub

 

[Jayanthi]

I tried with that ... I could'nt get those
will you please provide the code....

Cell A1 conatins AM_CM_0001
Cell B1 conatins AM_CM_1000

GIven value is AM_CM_0050


need to compare the characters then have to check whether the give
value is in that rang

 

[Don Guillett]

In your original post you said aaa_001 NOT aaa_aaa_001. Try this.

=VALUE(MID(G4,FIND("_",G4,FIND("_",G4)+1)+1,LEN(G4)-FIND("_",G4,FIND("_",G4))))