Solved: Looping through Dynamic Array

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.

[VBA]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

[/VBA]

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

Thanks a million

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.

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!

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)

[VBA]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
[/VBA]

you can use function for this

see attached file

mohanvijay,

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

Really appreciate it!

Cheers,
Leo