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".
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.
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