Macro to combine data based on IF,THENs?

This macro should work for you:

Sub createList()
' Declare variables...
Dim pgStart, pgEnd, newRow, counter As Integer
Dim myTitle, ansKey, pgRng, newString, string2 As String

' Select Sheet1 first cell w/data (A2)...
ThisWorkbook.Sheets(1).Activate
ActiveSheet.Range("A2").Select
' Initialize counter
counter = 0
' Loop through all rows of data and populate Sheet2...
Do Until IsEmpty(ActiveCell)
    ' Count data rows to process
    counter = counter + 1
    ' Get title...
    myTitle = ActiveCell.Value
    ' Get page start...
    pgStart = ActiveCell.Offset(0, 1).Value
    ' Get page end...
    pgEnd = ActiveCell.Offset(0, 2).Value
    ' Get answer key...
    ansKey = ActiveCell.Offset(0, 3).Value
    ' Determine page range and format accordingly...
    If pgEnd > pgStart Then
        pgRng = "pp. " & pgStart & "-" & pgEnd
    Else
        pgRng = "p. " & pgStart
    End If
' Concatenate data and populate Sheet2...
    newString = myTitle & ", " & pgRng & ", " & ansKey
    'Determine first empty row in Sheet2 to populate
    If counter = 1 Then
        newRow = ThisWorkbook.Sheets(2).UsedRange.Rows.Count
    Else
        newRow = ThisWorkbook.Sheets(2).UsedRange.Rows.Count + 1
    End If
    ' Populate Sheet2 accordingly (2 records if answer key present)...
    If ansKey <> "" Then
        newString = myTitle & ", " & pgRng & ", " & "answer key"
        string2 = myTitle & ", " & pgRng
        ThisWorkbook.Sheets(2).Cells(newRow, 1).Value = newString
        ThisWorkbook.Sheets(2).Cells(newRow + 1, 1).Value = string2
    Else
        ThisWorkbook.Sheets(2).Cells(newRow, 1).Value = newString
    End If
    ' Go to next row of data...
    ActiveCell.Offset(1, 0).Select
Loop
' Switch to Sheet 2 to show results...
ThisWorkbook.Sheets(2).Activate
ActiveSheet.Range("A1").Select
End Sub

Hope this helps,
theDude

theDude-

Thank you so much!! This is fantastic! You've saved me a lot of work in FileMaker. And thanks for including all the comments--they're a great learning tool.

One little thing I noticed after running the macro is that rows with nothing in the "Answer Key" column get returned with an ending comma; e.g.:

apples, p. 1,

If I study the code for a while (long while), I think I should be able to remedy that...but in the meantime, if you're able to help (once again), that'd be great.

Thanks again!

Originally Posted by theDude

This macro should work for you:

Hope this helps,
theDude

Yay, I think my change works:

' Concatenate data and populate Sheet2...
If ansKey = "" Then
newString = myTitle & ", " & pgRng
Else
newString = myTitle & ", " & pgRng & ", " & ansKey
End If