pan pipa

Hi,
I have just started coding in VB Excel. I am comparing two sheets, both with Date and Time Columns. These Col have to match when performing calculations comparing the values for that specific Date and Time.

Except i get numerous repeating TimeENT inputed into the Data Sheet. This happens at Line 31 after it completes one cycle. For example the DateENT&TimeENT would be 01/01/04 & 1:10AM in Line 34,
1 If Range("A" & J) <> DateENT(Count1) And ...
but when it comes down to 44
TimeENT(Count1) = Range("B" & J)
the Time will change to 1:07AM. Essentially going backwards. I can't seem to figure this one out. Let me know if you need more info.
Thank you.

pan pipa

I think it would be easier to include some sample code.
I am using this to develop trading strategies comparing multiple charts. Like i mentioned i get repeat Time entries in the Data Sheet, which is bad.

tinyjack

I had a quick look at your code. One thing that appears to be wrong is that you keep using Count1, but never increment the figure.

Could you try to explain simply, what needs to be written to sheet DATA.

TJ

pan pipa

I only use Count1 so that it can be matched to the Range() arrays in the IF Then statements (ie. Count1 is a 1x1 array- if i increment Count1 i get no data output). I don't know how to compare a number to a number in an array.

In sheet Data, i want it to write:

-Date [DateENT(Count)] being the date of CloseEXT from Sheet 1
-Time [TimeENT(Count)] being the time of CloseEXT from Sheet 1
-Profit/Loss [ProfitLoss(Count)] being the diff between CloseEXT and CloseENT at that time & date from Sheet 1

Sheet 2 is used to trigger the check in Sheet 1, but i only want the entry and exit prices, and the date & time of sheet 1 being inputed into Data sheet.

Does this make sense?
PP

tinyjack

I am still none the wiser, sorry.

Could you post another smaller example file (with just a hand full of entries on sheets 1 & 2), that has the output you would expect on the Data sheet.

It looks like you are using arrays that are not needed, but it is hard to follow your code.

TJ

pan pipa

Good idea, i made the numbers easy to follow with only a few lines of data. On the data sheet you will see first, what the code is giving me, and next to it what I am expecting. Notice the extra line.

I also too believe that I am using an array where I don't need to..but.. I plan to add more sheets and more columns variables (indicators) to this code, so I thought using arrays would be my best bet.

Thanks for you help TJ, it is much appreciated

PP

tinyjack

I have downloaded the file and I will have a look at it in more detail tonight. It looks to me from the quick look I have had now that your If ..Then and Do ... Loops are not interacting as you are expecting. I will try to come up with a simpler structure. Nesting loops and If .. Then with Goto statements can lead to very hard to follow code.

TJ

tinyjack

I have not been able to find the cause of your problem, but it is going to be something to do with either the interaction of your IFs and GOTOs or a variable not being updated as you expect. I will try to have another look at the weekend when I will have more time.

You might want to think about the following though:

You are doing this at present

You could do the following

I think this type of structure would make your code a lot more readable and easier to debug.

Sorry I cannot be more help at present.

TJ

pan pipa

TJ,
I appreciate your sense of humour and the pointers.

I do realize the fact that it is hard to read and trust me, it took my a few days to write it and make it spit the numbers I wanted, sort of.

Ideally what I want to do is set-up the IF Then equations in an array with a corresponding boolean outcome as to whether it is to be used in the subroutine. This would get rid of the IF Then statements which have been simplified for this post, they actually run three pages across(!)

Initially I had some help from friend and together we wrote the core., I should see him this weekend and maybe we might be able to hammer out an alternative solution. I will have an update on Monday.
cheers,
PP

pan pipa

I noticed where the problem may be originating.

In the following line
I set the DateENT(Count1) equal to range so that it can begin comparing the date & Time at J=3 in sheet 2. Otherwise DateENT will be given 12am as the starting point. It appears this equation is held as constant so when it comes to incrementing the Next J, it automatically equates DateENT = Range(J), so it bypasses my first j=j+1 loop.

So how can I compare the two without it reverting DateENT to the initial value?

pan pipa

Another issue I noticed is that I need to compare the Date & Time from Sheet 1 with the Date & Time from Sheet 2. However Sheet 2 has data for every other minute (2-Min Chart), whereas Sheet 1 has data for each minute (1-Min Chart).
When I compare Date&Time from Sheet 1 and the time does not exist in Sheet 2, I need to take the next closest time slot of Sheet 2.

Example:
After finding the CloseEXT(time) in Sheet 1 = 1:10, however since Sheet 2 is odd numbered I need to find the next row after 1:10, that being 1:11.

I think this where the problem exists. I have updated the file, and made some changes you recommended. Please ignore the previous attachments.