PDA

View Full Version : Solved: Looping through Dynamic Array



LeoLee
01-19-2011, 07:35 PM
Hi All,

I have created a Dynamic Array "CustomerID" by using =OFFSET functions. In cell A1, I have a value, let's say "12" and I am require to find the same value in cell A1 in my Dynamic Array "CustomerID".

Any ideal how can I do?

Alternatively, I have research a code from http://www.vbforums.com/showthread.php?t=157498

It seem somewhat similar just that I need to modify it abit. However, I do not understand how this code work and hence I could not modify it to my case. I would really appreciate if someone could help me to understand this and modify.

Public Sub bArrRemoveDuplicate(ByRef ByteArray() As Byte)
Dim LowBound As Long, UpBound As Long
Dim TempArray() As Byte, TempByte As Byte, Cur As Long
Dim A As Long, B As Long

'check for empty array
If (Not ByteArray) = True Then Exit Sub

'we need these often
LowBound = LBound(ByteArray)
UpBound = UBound(ByteArray)

'reserve check buffer
ReDim TempArray(LowBound To UpBound)

'set first item
Cur = LowBound
TempArray(Cur) = ByteArray(LowBound)

'loop through all items
For A = LowBound + 1 To UpBound
TempByte = ByteArray(A)
'make a comparison against all items
For B = LowBound To Cur
'if is a duplicate, exit array
If (TempArray(B) Xor TempByte) = 0 Then Exit For
Next B
'check if the loop was exited: add new item to check buffer if not
If B > Cur Then Cur = B: TempArray(Cur) = ByteArray(A)
Next A

'fix size
ReDim Preserve TempArray(LowBound To Cur)
'copy
ByteArray = TempArray
End Sub



I would really appreciate if someone could advise me on this.:help

Thanks a million

GTO
01-20-2011, 04:11 AM
The code you show appears to return unique/non-duplcated values, not look for where a value was located.

You might want to post a workbook sample (with fake data if sensitive) and show what we are trying to accomplish.

LeoLee
01-20-2011, 06:21 PM
Hi,

I have attached a quick demonstration of how my program requires.

Sheet 2 is the place where all the back-end stuff is happening.

1. In Column A, it is a record of Customer ID and I have create a Dynamic Array call CustomerDynamicArray using Offset.

2. In Column B, it is a number imported by Sheet 1. At all times, it will only contain 1 customer ID.

3. Column C and B is the results of using value in B2 to loop through CustomerDynamicArray to identify any duplication.

After countless hour of researching :banghead: , the nearest of looping through a dynamic array is the code I attached above.

Please help me :help I really really appreciate your help!

A million thanks!

LeoLee
01-20-2011, 08:29 PM
I have did a simple VBA to try to achieve my program, however, it seem there is some problem. Can you help me to debug my code?

CustomerDynamicArray is created in the workbook using =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Private Sub CommandButton1_Click()

Dim x As Boolean
Dim y As Integer
Dim i As Long

x = True

y = 2


For i = LBound(Array("CustomerDynamicArray")) To UBound(Array("CustomerDynamicArray"))
If CustomerDynamicArray(i) <> y Then
x = False
Exit For
End If
Next i
If x = True Then
MsgBox "Match"
Else
MsgBox "Item " & i & " does not match"
End If

End Sub

mohanvijay
01-21-2011, 06:05 AM
you can use function for this

see attached file

LeoLee
01-25-2011, 06:22 PM
mohanvijay,

A million thanks for the help! This is what I am looking for.

Really appreciate it!

Cheers,
Leo