gscarter
07-18-2008, 05:51 AM
Hi,
I have an array of customers called customer which are all unique, i also have a list of customers on a spreadsheet (column A for example), with a list of program versions next to them (column B).
I wish to iterate through each customer in the array, each time iterating through the customers on the spreadsheet, if there is a match i wish to change the array 'customer' to a 2d array, so the program version is stored next to the customer. One customer can be running more than one version.
e.g
customer(0) = Gary
customer(0)(0) = V1
customer(0)(1) = V2
This is how im trying to go about it at the moment.
Function uniqueCustomer(column As String) As String()
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
K = 1
J = 0
L = 0
If column = "A" Then
nextColumn = "B"
Else
nextColumn = "E"
End If
For Each cust In customer
M = 0
Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K + 1))).Value
customerVersion = ThisWorkbook.Worksheets("Sheet1").Range(nextColumn & Trim(CStr(K))).Value
If cusomter = currentCustomer Then
ReDim customer(UBound(customer),M)
customer(L)(M) = CStr(customerVersion)
M = M + 1
End If
K = K + 1
Loop While nextCustomer <> ""
L = L + 1
Next cust
uniqueCustomer = customer
End Function
I have copied the parts of the function that apply to the question, if i have left something out please let me know.
I don't think i am reDim'ing the customer() array correctly and frankly its really confused me, i may be going about it the totally wrong way.
Can anyone help me?
Gary
I have an array of customers called customer which are all unique, i also have a list of customers on a spreadsheet (column A for example), with a list of program versions next to them (column B).
I wish to iterate through each customer in the array, each time iterating through the customers on the spreadsheet, if there is a match i wish to change the array 'customer' to a 2d array, so the program version is stored next to the customer. One customer can be running more than one version.
e.g
customer(0) = Gary
customer(0)(0) = V1
customer(0)(1) = V2
This is how im trying to go about it at the moment.
Function uniqueCustomer(column As String) As String()
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
K = 1
J = 0
L = 0
If column = "A" Then
nextColumn = "B"
Else
nextColumn = "E"
End If
For Each cust In customer
M = 0
Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K + 1))).Value
customerVersion = ThisWorkbook.Worksheets("Sheet1").Range(nextColumn & Trim(CStr(K))).Value
If cusomter = currentCustomer Then
ReDim customer(UBound(customer),M)
customer(L)(M) = CStr(customerVersion)
M = M + 1
End If
K = K + 1
Loop While nextCustomer <> ""
L = L + 1
Next cust
uniqueCustomer = customer
End Function
I have copied the parts of the function that apply to the question, if i have left something out please let me know.
I don't think i am reDim'ing the customer() array correctly and frankly its really confused me, i may be going about it the totally wrong way.
Can anyone help me?
Gary