Unrecognisable logical error

Hi! In the following code, I want to compare 'time'(in column 2 and column 4) of all 8002 rows if the cells (i,6) and (j,6) are equal. In the Excel sheet for which the macro has been written,
Column 2 contains Start-time(of the beginning of the event).
Column 3 contains the value of the column 2 values in type 'Number'.
Column 4 contains End-time and Column 5 contains the values in type 'Number'.
If the event is being recorded more than once for the same duration of time (for the same column 6 value), the jth row is to be deleted or before deleting, (j,4) has to be transposed to (i,4), in case (j,4) is larger than (i,4).

Please Login or Register  to view this content.

Every If statement contains atleast 2 lines in the body, so i used If-Then-Else in the same lines and no End If statements.
The problem tht i am encountering is, that ,i am performing only 2 operations, 'transpose' or 'delete' or both. But nowhere am i changing the values(incrementing,decrementing). The value of (j,4)is itself being altered to some very random time value. Pleas help!

Blimey! A true example of spaghetti code! I can't see many people taking on that code, you need to restructure it and remove all of the Gotos to get some meaningful help.

I'm about half-way through re-writing it, but it's really, really hideous.

I haven't seen code like that since I taught myself to code in BBC BASIC circa 1984.

OK, I think I've done this - although without data to test it against it's pretty much a guess.

First off, some coding tips:

1. It's generally agreed that it's best to avoid GoTo statements - they make the flow of the code much harder to understand. Use structured code, such as block If statements to eliminate them.

2. Don't use single letter variable names and use Dim to declare variables before using them

3. Nested loops are slow - in your code the inner loop will be executed more than 32,000,000 times. Excel has tools, such as the Find method, to avoid having to repeatedly compare cell against cell looking for a match.

4. Did I mention the GoTo? Good, I really mean that one.


Anyway, try this (make sure you save a copy of your data first, as it's untested):

Please Login or Register  to view this content.

Originally Posted by Andrew-R

1. It's generally agreed that it's best to avoid GoTo statements - they make the flow of the code much harder to understand. Use structured code, such as block If statements to eliminate them.

Okay, let's start a war .

I disagree, and I think it is an area of debate rather than general agreement. Whilst I personally avoid them wherever possible, I cannot get exercised about Gotos in code. If the code is well structured, and the Gotos are used sparingly and in a logical flow, I cannot see any argument against them. What I think is unarguable is that usage such as shown here is horrible, terrible programming practice, and really should be avoided.

@Bob - the nadir of my BBC days was when I tried to code a mini-operating system which worked on 4 letter commands. The opening lines looked like this:

Please Login or Register  to view this content.

Basically when the user typed in their 4 letter command the program would poke those 4 letters in place of the @ symbols on line 20 of the code and then GOSUB to that line call the procedure with that name and then use the RETURN to go back to the branch point.

Amazingly it worked, but I wouldn't have liked to try to debug it.

Originally Posted by Andrew-R

OK, I think I've done this - although without data to test it against it's pretty much a guess.

BTW, whilst I still think you are mad, and whilst I think your code is pretty much as spaghetti as the original, I am in awe that you took it on, and even deeper in awe that you finished it

I agree - it's nasty code (note the Sub name), because without any proper data to work with the best I could do was re-write pretty much what was there, rather than doing it properly. Going from the OP's description there is obvious redundancy (e.g. column 3 and column 5 contain the same data as columns 2 and 4, just formatted differently) and tortuous routes taken for no good reason (e.g. comparing the hours and minutes of two cells separately to work out if one is greater than the other - was there a good reason for doing this?)

On the subject of GoTo - if your philosohpy is to avoid them whenever possible then I maintain you'll never use them. I made the leap from BBC BASIC to a structured language (Pascal) in 1987 and in the 25 years since then I've never had to use a GoTo, except in error trapping. I've done quite a lot of coding in that time and while I admit it may be the case that I've just never coded something which needs GoTos I'd be tempted to say they're simply not required.

Originally Posted by PingPing

Give that man a medal.

Pah, anything at all from the OP would have been nice.