A little more effort...

[darno]

Dear TOM,

If you remember that the solution given under was recommended by you
and which worked great for my query. Now a little more help is
required, I am sure (100%) you will be able to answer this as well.

In the same query I want to get the total of my date range Column. As
this is understood that the 2 input dates that are entered do not have
a limit they can be based on any starting cell to any ending cell. So
my query is to calculate no of cells used between these 2 dates
including starting and ending date as well. for example the column2
contains all date entries. If the first date that I entered in input
box was on cell b2 and the end date was on b30 then I want to to know
that how many total no of cells are there between these two dates. In
simple words I need the total SUM of those cells used in from starting
date to ending date..


<<<OLD MACRO>>>

Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng As Range
sStart = InputBox("Enter Start Date")
sEnd = InputBox("Enter End Date")

If IsDate(sStart) And IsDate(sEnd) Then
res = Application.Match(CLng(CDate(sStart)), Range("A1:A365"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("A1:A365"), 0)
If Not IsError(res) And Not IsError(res1) Then
Set rng = Range(Range("A1:A365")(res), Range("A1:A365")(res1))
rng.Resize(, 6).BorderAround Weight:=xlMedium, ColorIndex:=3
End If
End If
End Sub


By the way i did post the same message in my previous thread but no
response thats why i am writing it in a new thread.


Regards,


Darno

 

[Tom Ogilvy]

You want the number of cells:

Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng As Range
sStart = InputBox("Enter Start Date")
sEnd = InputBox("Enter End Date")

If IsDate(sStart) And IsDate(sEnd) Then
res = Application.Match(CLng(CDate(sStart)), Range("A1:A365"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("A1:A365"), 0)
If Not IsError(res) And Not IsError(res1) Then
Set rng = Range(Range("A1:A365")(res), Range("A1:A365")(res1))
rng.Resize(, 6).BorderAround Weight:=xlMedium, ColorIndex:=3
msgbox "Number of cells in " & rng.Address & " is " & rng.count
End If
End If
End Sub

 

[darno]

Dear TOM,

Thanks for your help and i really appreciate the way you put an effor
to answer all needy people like me. I really admire your parents an
teachers who gave you such manners and teachings, to never say NO t
anybody and keep spreading smiles to all. I am impressed. Please kee
this thing going and i tell you what you will end up gaining more an
more. You are doing a great job. may GOD keep you moving with success
(AMIN)

Thanks for sending me the solution, but to tell you the good news tha
i solved that query by myself, they solution that you gave me wa
showing the results in a message box, but what i need was the total o
res and res1 in cell lets say C3. and i did it by declaring an othe
variant named as tak and added res and res1 values in this variable
later on showed it on the cell address C3. pretty simple but it worked
your solution is excellent and i am thinking of using it in later stag
of this program.

I have a formula query, i need to check more than 10 conditions, i kno
it can be done by usinf AND/ OR But i am unable to figure out how.
have more than 10 conditions to check and all return same cell value i
the cell. for example if cell c3 = 84 and cell d3 = 21, then displa
message GOOD, else OK. if c3= 84 and cell d3 = 22 then display BAD
else OK. and the conditions keep on going on and on. Please help m
out.


Regards and Best wishes,


darn

 

[Tom Ogilvy]

set r = Range("C3")
set s = Range("D3")
set t = Range("E3")

if r = 84 and s = 21 then
sStr = "Good"
Elseif r = 84 and s = 22 then
sStr = "Bad"
Esleif r = 83 and s = 21 then
sStr = "something else"
' and so forth
else
sStr = "OK"
End if


t.Value = sStr
' o r
' msgbox sStr

you didn't explain what you mean by display.

 

[darno]

Dear Tom,

Thanks for the help again, Well i am sorry i was unable to clearify to
you what exactly i was looking for. Well by Displaying i meant to
display through conditional formatting in a worksheet cell. I want this
query to be solved via normal worksheet formula, not through MACRO. I
am writing you the existing formula which i have, but i am unable to
add more conditions to it:

=IF(OR
(I208>18;M208>66;O208>30;Q208>240;S208>90;U208>800);"FINE";IF(Y207="P";"PERFECT";IF(AND(AC207>=1;AC207<=7);IF(AC207=-AD207;IF(X208<>1;"BAD");"BAD");"BAD")))

This was my existing formula, if i were to add more conditions it wont
work. Please help me out. The conditions are around 10, and i have
already told you few of those, please help me out.


Regards,


Darno