none
Update array row value using inner loop of the same array RRS feed

  • 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
          d.Add c1, c3
        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 mogulman52 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.

    Thanks in advance.

    Friday, April 15, 2016 12:04 PM