Comparing Array Objects

[manrique83]

Hi,

I am looping through the array and comparing values but it doesn't seem to work? Can someone please help? Ive looked at it for hours and Im sure its something dumb.

For i = 1 To y
If myArray(i, 4) <> myArray((i + 1), 4) Then
For j = 1 To y
For k = 1 To 4
ThisWorkbook.Worksheets("Move").Cells(a, k).Value = myArray(j, k)
Next k
a = a + 1
Next j
Exit For
End If
Next i

 

[PHV]

I guess you want to replace this:
For j = 1 To y
By this:
For j = 1 To i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[manrique83]

PHV thanks for the tip.

The problem is that it goes into the first IF statement at all times, whether the values in the array are equal or not. Is there an array property I'm missing?

Thanks,
Hector

 

[PCLewis]

The Exit For in your code is not good.

I assume VB is clever enough to unwind the If..Then branch that you are in at the time but it's not a good practice in my opinion. Especially when more logical alternatives are available. If you put the Exit For after the If..Then, it would be more logical and would cost you nothing in terms of speed.

The issues I see with the code are:
Possible out of bounds on myArray in the first comparison i.e. when i=y, does myArray(i+1,4) exist?

The exit For is unecessary if you used a Do..Loop but that's a programming style more than a defect.

Ambiguous definition of variable 'a' - I assume you declare it as Integer or whatever elsewhere. If you don't declare it at all then VB probably is clever enough to work out that it's an Integer or Long but it will have a starting value of 0. Since 0 is not a valid row in the .Cells function, you will get an error here. But you never mention an error so I assume you have a set properly elsewhere.

So other than these, there is nothing wrong with the code. It will do what it says it will do. You need to write back to the forum with your intended behaviour of the code and what the actual behaviour is.

 

[manrique83]

PCLewis,
Thanks for the reply. It helps to get advice on my programming style.

All my variables are initialized to 1, with the exception of 'y'. It is the array length. I have 'Option base 1' so I won't get more confused with the 0s.

The problem I have is that even when the values in array are equal, it still goes into the first IF statement. In other words it is not comparing the values at all.
For example:
myArray(1,4) = y
myArray(2,4) = y
The first If statement should detect that these are equal but it enters the statement regardless and moves these values to the spreadsheet. Why?

 

[PHV]

How is declared and populated myArray ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PCLewis]

If you are saying that myArray(1,4) = myArray(2,4) and that the instructions after the Then statement are being run there are only two possibilities.

1) you made a mistake - under no circumstances will Access do that. Perhaps myArray is not a simple data type - maybe you have declared it as an array of objects. I think that is not the issue however because that does not fit the contect of what you are doing. Perhaps they are both strings and one has some extra characters..

2) You have a "On Error Resume Next" statement in your code and the IF statement is causing an error which means that Access will run the very next instruction. In this case it would look like the IF statement was being entered when in fact it is just an "accident".

There might be other ways to get the behaviour but I can't think of them.

Let us see the declarations of all your variables. It is essential.

 

[manrique83]

Thank you both PCLewis and PHV.

Your questions and comments helped me find out what was wrong. It was in my For Loop logic I only need to scan the array for Array.Length -1 since I am already comparing the last entry in the array when I add i+1.

Thank you very much, sometimes the solution is found by asking questions.
Thanks!!!