Output list based on value in a cell

  • Thread starter Thread starter vegan
  • Start date Start date
V

vegan

I hope someone here can help. I have next-to-zero experience with
Excel macros.

I have a large spreadsheet that was used to track workshop attendance
for several people throughout 2005. It has names in Column A and
workshop names in Row 2.

Throughout the year, if someone attended a workshop, a number was
placed next to their name under the workshop, representing the number
of hours they attended.

Now I need to somehow output to a list each persons name, ONLY the
workshops they attended, and their total hours attended.

For example: If F5 contains a value, I need to output A5, F2, F5 (that
is, Name, Workshop Name, Hours attended.)

Is this possible in Excel? Something you could walk a macro newbie
thorough?

Thanks in advance for any help you can provide.

Brett
 
Hi Brett,

I have created this macro that does the trick
In short:
It creates a new sheet where the result will come
It places headings on this new sheet.
It walks throu you sheet and copies the
- name
- workshop
- hours
to the new sheet.



Public Sub TotalsPerAttendee()
Dim lRowScan As Long
Dim lColScan As Long

Dim lRowReport As Long
Dim sCurrent As String
'
sCurrent = ActiveSheet.Name
ThisWorkbook.Sheets.Add
ActiveSheet.Name = "AttendeeReport"
Range("A1").Value = "Name"
Range("B1").Value = "WorkShop"
Range("C1").Value = "Hours"
lRowReport = 2
Sheets(sCurrent).Activate

lRowScan = 3

Do
lColScan = 2
Do
If Not IsEmpty(Cells(lRowScan, lColScan)) Then
Sheets("AttendeeReport").Cells(lRowReport, 1) = Cells(lRowScan,
1)
Sheets("AttendeeReport").Cells(lRowReport, 2) = Cells(2,
lColScan)
Sheets("AttendeeReport").Cells(lRowReport, 3) = Cells(lRowScan,
lColScan)
lRowReport = lRowReport + 1
End If
lColScan = lColScan + 1
Loop Until IsEmpty(Cells(2, lColScan))
lRowScan = lRowScan + 1
Loop Until IsEmpty(Cells(lRowScan, 1))

End Sub


Hoop this helps


Executor.
 
Back
Top