VBA Excel - Fill columns based on values from another column and conditional-CodePudding

I have solved this problem in Python, but I would like it in VBA so that anybody can just cut/paste it into their workbooks and use it, since most of the people I work alongside are not Python-literate, or even novices in the most liberal sense of the word when it comes to programming.

The columns of interest (see below) are B, C, D. Column B represents levels of separation away from the top order (Column A). If any value in Col B is 1, Col D of that row == A (John). When BX > 1 (X is any row number), DX takes the value of the first row in B that is exactly 1 less than BX. For instance, D4 == B3, but D8 == B7, B9 == B6 and so on.

In python, I solved it like this (I know it's not very elegant):


import pandas as pd
df = pd.read_csv('workbook.csv', header=0)

levels = df['col2'].to_list()
child = df['col3'].to_list()
parent = []

length = len(levels)
indexing_container = {}

for idx in range(length):
    if levels[idx]==1:
        parents.append('John')
    elif levels[idx]>1:
        index_container.update({str(levels[idx]):child[idx]})
        parents.append(index_container[str(levels[idx]-1)])

df['Parents'] = parents

This works great, the problem is I don't know VBA. Reading the docs in the meantime, but not sure how it will go. How do I write this in a VBA script?

We use Office 2019, if that makes a difference.

CodePudding user response：

The following code is rather simple: It created an array of names. The column B gives the index into the array, column C the name.
The code loops over all rows, fills the name into the array and then looks for the name of the previous index.

Sub xy()
    Const MaxNames = 10
    
    Dim wb As Workbook, lastRow As Long
    Set wb = Workbooks.Open("workbook.csv")
    
    Dim names(1 To MaxNames) As String
    With wb.Sheets(1)
        names(1) = .Cells(2, 1)
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).row
        Dim row As Long
        For row = 2 To lastRow
            Dim index As Long
            index = .Cells(row, 2)
            names(index) = .Cells(row, 3)
            If index > 1 Then index = index - 1
            .Cells(row, 4) = names(index)
        Next
    End With
End Sub

Note that I simply open the CSV file as workbook. Depending on the content of your CSV file and the settings in your Excel, this might or might not work. If you have problems (eg a whole line of data is written into one cell), you need to figure out which method of reading the data fits your need - lots of information on SO and elsewhere about that.