# Update array row value using inner loop of the same array

• ### Question

• This post  is similar to my previous thread. trying to update values in  sorted sequential row in array.Have duplicated customer no. , need to find the last row of each customer.no and update the corresponding value of volume column to  last column for all customer.no's. sample given below.

For lngcount= 1 To UBound(arrCustomerCalculation, 1)
lngOutputRowCount = 1
For lngcounter2 = 1 To UBound(arrCustomerCalculation, 1)
blnMatch = False
For lngInnerLoopCounter = (lngcounter2) To 1 Step -1
If arrCustomerCalculation(lngInnerLoopCounter, 1) = arrCustomerCalculation(lngcounter2 + 1, 1) Then
blnMatch = True
Exit For
End If
Next lngInnerLoopCounter
If Not blnMatch Then
lngOutputRowCount = lngOutputRowCount + 1

FinalAssigned = arrCustomerCalculation(lngcounter2, 18)

Exit For
End If
Next lngcounter2
arrCustomerCalculation(lngcounter, 21) = FinalAssigned
Next lngcounter
Tried with above code. can anyone look on this and suggest me a solution. Please suggest me only, array related solutions.

Thursday, March 24, 2016 2:03 PM

### All replies

• Use a formula - for a table in A:C starting in A1, use this in C2, copied down:

=INDEX(B:B,MATCH(A2,A:A,FALSE)+COUNTIF(A:A,A2)-1)

Thursday, March 24, 2016 2:48 PM
• Hi Bernie Deitrick,

Any possibility of doing the same in array dimension in VBA code?

Monday, March 28, 2016 1:35 PM
• You need to post more of your code, especially that code that defines your arrays.
Monday, March 28, 2016 4:55 PM
• Hi ,

Below is the code which defines the array.

Set wssheet = ThisWorkbook.Sheets("Customer Information")

wssheet.Activate
'setting the customer details in range
wssheet.Range("A1:C23").Select
'naming the range
Selection.Name = "CustomerList"
Set rngCustomerList = wssheet.Range("CustomerList")
ReDim arrCustomerCalculation(1 To rngCustomerList.Rows.Count, 1 To rngCustomerList.Columns.Count) As Variant
'moving the value to array
arrCustomerCalculation = rngCustomerList.Value

For lngcount= 1 To UBound(arrCustomerCalculation, 1)
lngOutputRowCount = 1
For lngcounter2 = 1 To UBound(arrCustomerCalculation, 1)
blnMatch = False
For lngInnerLoopCounter = (lngcounter2) To 1 Step -1
If arrCustomerCalculation(lngInnerLoopCounter, 1) = arrCustomerCalculation(lngcounter2 + 1, 1) Then
blnMatch = True
Exit For
End If
Next lngInnerLoopCounter
If Not blnMatch Then
lngOutputRowCount = lngOutputRowCount + 1

FinalAssigned = arrCustomerCalculation(lngcounter2, 18)

Exit For
End If
Next lngcounter2
arrCustomerCalculation(lngcounter, 21) = FinalAssigned
Next lngcounter
Tuesday, April 5, 2016 8:31 AM
• Hi Bernie Deitrick,

able to get solution for this?

Wednesday, April 13, 2016 5:28 AM
• Here is a simple way to do it using the data dictionary (hash).  Be sure to add reference to Microsoft Scripting Runtime

Option Explicit
Option Base 0
Option Private Module

Sub Test()

Dim ws As Worksheet
Dim d As New Scripting.Dictionary  'Add reference to Microsoft Scripting runtime
Dim k As Variant
Dim i As Long
Dim c1 As String
Dim c3 As String

i = 2  'Start row
Set ws = Sheets("Customer Information")
While ws.Cells(i, 1) <> ""
c1 = ws.Cells(i, 1)
c3 = ws.Cells(i, 3)
If d.Exists(c1) Then
d(c1) = c3
Else
End If
i = i + 1
Wend
k = d.Keys
For i = 0 To UBound(k)
Debug.Print k(i) & ", " & d(k(i))  'Immediate Window
Next i
End Sub

• Edited by Thursday, April 14, 2016 1:54 PM
Thursday, April 14, 2016 1:41 PM
• Hi mogulman52,

Bernie Deitrick already suggested me a work around for this, but I need to do this in array. I had done other logic in array, but this part alone is pending and I am not able to do.

Please let me know if  this can be done in array.

I had pasted the sample code above.