nwm-excel-macro.vba

' We declare a bunch of variables to hold which column each piece of data we want is
Public columnRange As String
Public notesColumn As String
Public ageColumn As String
Public maritalStatus As String
Public incomeColumn As String
Public occupationColumn As String
Public firstNameColumn As String
Public lastNameColumn As String
Public employerColumn As String

' Boostrap is a function we call to assign values to our variables when our function is run
Function Bootstrap()
columnRange = "A:AS"
firstNameColumn = "A"
lastNameColumn = "B"
notesColumn = "W"
ageColumn = "K"
maritalStatus = "M"
incomeColumn = "L"
occupationColumn = "Q"
employerColumn = "O"

End Function

' PrepareForExport is the Sub Procedure that runs when you click the Macro button in the worksheet.
Sub PrepareForExport()
' Calls Bootstrap to assign values to our column variables as we can use them later in the code
Bootstrap
' We grab the worksheet with the data we want from the Active Workbook (i.e. opened workbook)
Set sh = ActiveWorkbook.Worksheets("Market Identifier Worksheet")
' We declare the variable rw as Range (a group of cells) this well represent each row in the workbook as we loop over them
Dim rw As Range

' We declare the variable numColumns as an integer to get a count of the number of columns in each row, later to be used to select a new Range
Dim numColumns As Integer
numColumns = sh.Columns(columnRange).Count

' Starting at row 2, loop over the rows in the worksheet until we get the end of the notebook
For idx = 2 To sh.Rows.Count
    ' Set rw to the current row  
    Set rw = sh.Range(Cells(idx, 1), Cells(idx, numColumns))
    ' If the current row's firstName and lastName column are emtpy assume we've iterated over every row and Exit the macro
    If (rw.Cells(1, firstNameColumn).Value = "") And (rw.Cells(1, lastNameColumn).Value = "") Then
        Exit For
    End If
    
    ' Call the ValidateRow function and pass it the current row
    ValidateRow rw
    ' Call the GenerateAutomatedNotes function and pass it the current row
    GenerateAutomatedNotes rw
    
' Go to the next row
Next idx
End Sub

' ValidateRow is a function that accepts a Row as a parameter and is used to validate the presence of FirstName and LastNAme
Function ValidateRow(rw As Range)
' If the firstName column is empty set it to N/A
If rw.Cells(1, firstNameColumn).Value = "" Then
    rw.Cells(1, firstNameColumn).Value = "N/A"
End If
' If the lastName column is empty set it to N/A
If rw.Cells(1, lastNameColumn).Value = "" Then
    rw.Cells(1, lastNameColumn).Value = "N/A"
End If


End Function

' GenerateAutomatedNotes is a function that accepts a range/row and generates a new value for the notes columns
Function GenerateAutomatedNotes(rw As Range)
' Set the notesCell to be the current rows note cell 
Set notesCell = rw.Cells(1, notesColumn)
' Assign the value of the notes cell to be any exisiting text + the values from our other columns
notesCell.Value = notesCell.Value & vbNewLine _
 & "---------- Granum QS Info ---------" & vbNewLine _
 & "Marital Status: " & rw.Cells(1, maritalStatus).Value & vbNewLine _
 & "Age: " & rw.Cells(1, ageColumn).Value & vbNewLine _
 & "Income: " & rw.Cells(1, incomeColumn).Value & vbNewLine _
 & "Occupation: " & rw.Cells(1, occupationColumn).Value & vbNewLine _
 & "Employer: " & rw.Cells(1, employerColumn).Value & vbNewLine _


End Function