excelvbacell

Excel VBA code use value of a cell as the row number position for cell values


I have an Excel spreadsheet with exported sales information. I want to summarize the information onto another tab. Basically Summary Sheet column A will list a Customers name, then the items they purchased. Then the next Customer and the items they purchased, in the same column.

I was able to figure out how to extract all the customer names and how often they showed up. But I can't figure out how to copy their names, while leaving space for their items to be listed under their name.

I have step 1 figure out, but need help with step 2
enter image description here

I was thinking of using a function that would let me loop through the list and use the values in column B as the row position when pasting the Customer Names.

Something like

Dim i As Long
Dim j As Long
Dim NumRows As Long

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count

Cells(1,4).Value = Cells(1,1).Value

j="B1"

For i=2 To NumRows

    Cells([j+3],4).Value = Cells("A[i]",1).Value

    j=j+"B[i]"+2

Next i

Solution

  •     Dim CustomerRange As Range ' CustomerName-StartPosition
        Dim GoalRange As Range     ' Range, where it is Printed
        Dim NumRows As Long        ' Number of Customers
        Dim i As Long              ' For CustomerNames
        Dim j As Long              ' For CustomerAmount
        Dim k As Long              ' For Whole offset of GoalRange
    
        Set CustomerRange = Range("A1")
        Set GoalRange = Range("D1")
        NumRows = Range(CustomerRange, CustomerRange.End(xlDown)).Rows.Count
    
        CustomerRange.Offset(NumRows, 1).Value = CustomerRange.Value ' Dont know why you want this but here it is
    
        For i = 0 To NumRows - 1
            GoalRange.Offset(i + k, 0).Value = CustomerRange.Offset(i, 0).Value
            k = k + 1
            For j = 1 To CustomerRange.Offset(i, 1).Value
                GoalRange.Offset(i + k, 0).Value = CustomerRange.Offset(i, 0).Value & " Item " & j
                k = k + 1
            Next
        Next