Note Summay with a twist

[austin350s10]

I am working on a large document where the user has the option of entering notes in many different cells on the worksheet. I would like a way pull out all the text they enter in a clean way and summarize it. When I say "clean" I mean locate only the cells they entered a note into and summarize it, in order, on a different sheet.

Each not section has a label associated with it so I was also wondering if the label could be plugged in and then the note. Then the next label and note...etc...

Example of Large Workbook

C10 = Label1 AC13 = Note Section1 (has text in it entered by user)
C24 = Label2 AC30 = Note Section2 (has no text in it)
C43 = Label3 AC45 = Note Section3 (has text in it entered by user)
C70 = Label4 AC78 = Note Section4 (has no text in it)
.........

Example of Summary Sheet

A1= "Lable1: "&AC13 +
CHAR(10)&"Label2: "&AC45
......

Is there any way to do something like this?

 

[Ruddles]

Gotcha! Try this, inserting the row numbers you're interested in in the iRequired Array list:-

Option Explicit
 
Const DataLabel As String = "C"     ' column where the labels are located
Const DataNote As String = "AC"     ' column where the notes are located
Const DataRow As Integer = 186      ' first row containing the notes
Const SummaryCell As String = "A1"  ' where we write the summary
 
Public Sub WriteSummary()
 
  Dim iPtr As Integer
  Dim iRequired As Variant
  
  iRequired = Array([COLOR=red]188, 200, 220, 232, 284[/COLOR])   ' CSV list of required cell row numbers
  
  Application.ScreenUpdating = False
  
  Range(SummaryCell).ClearContents
  
  For iPtr = 1 To UBound(iRequired)
    If Not IsEmpty(Cells(iRequired(iPtr), DataNote)) Then
      If IsEmpty(Range(SummaryCell)) Then
        Range(SummaryCell) = MergedValue(iRequired(iPtr), DataLabel) & ": " & Cells(iRequired(iPtr), DataNote)
      Else
        Range(SummaryCell) = Range(SummaryCell) & vbLf & MergedValue(iRequired(iPtr), DataLabel) & ": " & Cells(iRequired(iPtr), DataNote)
      End If
    End If
  Next iPtr
 
  Application.ScreenUpdating = True
    
End Sub
 
Private Function MergedValue(argRow, argColumn) As String
 
  Dim iScan As Integer

  For iScan = argRow To DataRow Step -1
    If Not IsEmpty(Cells(iScan, argColumn)) Then
      MergedValue = Cells(iScan, argColumn)
      Exit Function
    End If
  Next iScan
 
End Function

 

[austin350s10]

Hey...think you got it! Im gonna test it in the real workbook soon and get back to you. One thing I did notice though is that the Summary is written within the same sheet. Is there a way to send the summary to a different sheet?

Just for S&G's I tried to change the following line of code with no luck tho.

Const SummaryCell As String = Worksheets("Sheet2").Range("A1")