Insert data on sub-header change
 

I have header row, B12=€�Fund:€� & C12=€�Fund Name€�. Followed by one blank row.
The rows following contain employee names (B), period start (C), period end
(D), amount (E) and status (F). The employee list is variable. The four
rows after the end of the last employee name (same employee can be repeated)
are blank and the next Fund heading starts and so on for 20-30 funds.

This is a reported created in another system.

I need to create a summary for each employee (can be a member of several
funds) with total contributions per fund.

Currently, I add a helper column (After E) and copy a reference to the fund
name down to the last employee. Ive created a list of employees (repeated
only for those with more than one fund) on a separate sheet and use
sumproduct to calculate the fund summaries. This is tedious. Is there a way
of creating the helper column and inserting the fund name for each change in
the header row using VB?

--
Jim

Insert data on sub-header change
 

Thanks Nigel,

Once I removed the UCase it worked perfectly. I'm very grateful.


--
Jim


"Nigel" wrote:

The following will find and fill the fund name into column G, into all
subsequent non-empty rows until the fund name changes. I reference the
active sheet, so to run this code select the sheet first then run the code.

Sub FillHelper()

Dim lLastRow As Long, lRow As Long
Dim sFundName As String

With ActiveSheet
lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

lRow = 12
Do While lRow <= lLastRow
If UCase(Trim(.Cells(lRow, "B"))) = "FUND" Then sFundName = .Cells(lRow,
"C")

If Len(Trim(.Cells(lRow, "B"))) 0 Then .Cells(lRow, "G") = sFundName

lRow = lRow + 1

Loop
End With
End Sub

--

Regards,
Nigel




"Jim G" wrote in message
...
I have header row, B12=€�Fund:€� & C12=€�Fund Name€�. Followed by one blank
row.
The rows following contain employee names (B), period start (C), period
end
(D), amount (E) and status (F). The employee list is variable. The four
rows after the end of the last employee name (same employee can be
repeated)
are blank and the next Fund heading starts and so on for 20-30 funds.

This is a reported created in another system.

I need to create a summary for each employee (can be a member of several
funds) with total contributions per fund.

Currently, I add a helper column (After E) and copy a reference to the
fund
name down to the last employee. Ive created a list of employees
(repeated
only for those with more than one fund) on a separate sheet and use
sumproduct to calculate the fund summaries. This is tedious. Is there a
way
of creating the helper column and inserting the fund name for each change
in
the header row using VB?

--
Jim