gscarter
07-17-2008, 07:51 AM
Hello,
I am using Excel within Office XP (2002).
Im currently doing a project in which i copy two spreadsheets into one, and then use that data to create arrays of unique customers. I have created a function (below) that will do this.
Function uniqueCustomer(column As String) As String()
Dim I As Integer
Dim J As Integer
Dim flag As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
I = 1
J = 0
ReDim customer(J) As String
customer(J) = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value
Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I + 1))).Value
flag = 0
For Q = 0 To J
If customer(Q) = currentCustomer Then
flag = flag + 1
End If
Next Q
If flag = 0 Then
ReDim Preserve customer(J + 1)
customer(J + 1) = currentCustomer
J = J + 1
End If
I = I + 1
Loop While nextCustomer <> ""
uniqueCustomer = customer
End Function
The problem i have is i dont know whether i have correctly returned the array from the function (its all i could work out from the interent), and if so how do i reference that array in another function?
This may seem a silly question but its got me stumped.
Thanks
Gary
I am using Excel within Office XP (2002).
Im currently doing a project in which i copy two spreadsheets into one, and then use that data to create arrays of unique customers. I have created a function (below) that will do this.
Function uniqueCustomer(column As String) As String()
Dim I As Integer
Dim J As Integer
Dim flag As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
I = 1
J = 0
ReDim customer(J) As String
customer(J) = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value
Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(I + 1))).Value
flag = 0
For Q = 0 To J
If customer(Q) = currentCustomer Then
flag = flag + 1
End If
Next Q
If flag = 0 Then
ReDim Preserve customer(J + 1)
customer(J + 1) = currentCustomer
J = J + 1
End If
I = I + 1
Loop While nextCustomer <> ""
uniqueCustomer = customer
End Function
The problem i have is i dont know whether i have correctly returned the array from the function (its all i could work out from the interent), and if so how do i reference that array in another function?
This may seem a silly question but its got me stumped.
Thanks
Gary