Hello, I need help with my code, it is supposed to sum column G if column B are values contained in list "aircrafts", and if the date in column A is equal to today-90 days.
I put a counter "i", So the code is supposed to loop through the calculation to "myvar" and stops when myvar< 3:00. When it stops, it should return a msgbox with the value of i.
This code is returning a type mismatch error.
Any help?
Please Login or Register to view this content.
This code doesn't error, and it produces a result - not sure if it's the right one though.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Thanks Norie, it does not return an error, but it is always returning 1. In this sample, the answer should be 79.
Last edited by chiidzzz; 01-29-2013 at 08:56 AM.
Why should it return 79?
There's only 4 records on the sheet 'pilot1'.
The values in column G are all less than 3:00.
If you try this formula in a random cell,the answer will be 2:00 which is less than 3:00 (date2)Please Login or Register to view this content.
so "i" in this case is 79...
I hope I am clear enough.
That isn't the formula in the code.
I showed you that the code result in the sample must be 79.
The code you wrote always return 1, disregarding the records on the sheet 'pilot1' and the values in column G.
I'm confused.
If the formula you posted gives the result you want, why not use it?
Also, why are you looping?
On the first iteration of the loop this is the formula that's evaluated.
Formula:
Please Login or Register to view this content.
The purpose of this code is to calculate after how many days "i", the sum of the hours flown in the last 90 days will be less than 3:00.
This is why I want in the reply in the msgbox to be the last value of "i", where the criteria is met (myvar < date2).
Am I making myself clear enough? or should I explain more?
Have I been clear in explaining what's happening in the code?
If you wanted to evaluate this formula in the code,
Formula:Please Login or Register to view this content.
you would need this.
Please Login or Register to view this content.
sorry Norie, this is not what I mean.
The number 79 applies only in this example.
When I will make more entries , this number will change.
You said On the first iteration of the loop this is the formula that's evaluated
OK. But it should not stop because the criteria is still not met. In this case myvar = 3:00 which is not less than date2.Please Login or Register to view this content.
So the code should continue and the second iteration should be
Also in this case, the criteria is still not met...Please Login or Register to view this content.
So it will continue until "i" will become 79 in this case
where the condition will be true: myvar(2:00) < date2 (3:00)Please Login or Register to view this content.
The value of "i" represents after how many days myvar will be less than 3:00
This is the logic I am trying to achieve, knowing that the syntax is all wrong.
Please Login or Register to view this content.
The value of "i" represents after how many days myvar will be less than 3:00
This is the logic I am trying to achieve, knowing that the syntax is all wrong.
Please Login or Register to view this content.
I think there might be a problem comparing the result of the formula to date2.
They appear to be the same but VBA isn't treating then as such.
I don't know why that is but I'll check it out.
Thank you !!
I've incorporated the comparison in the formula being evaluated.
The formula now returns a boolean value which we use to determine when to end the loop.
When I run it I get 79, but you should check it out with other data.
Please Login or Register to view this content.
Thank you Norie, it works and I tried it with different data. Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks