Help with VBA Excel code

Last post was 11 Oct 2009 at 05:01

code , excel 2003 , vba Townsville, Australia

- Reply
11 years ago

by KDHTSVAU

Kevin Hubbard

Townsville, Australia

Joined 11 years ago
I'm creating a report that picks up which units are being studied by a certain participant. They pick 10 units out of a possible 16 I have list of unit labels along a row (B1:Q1) List of participants in column (A2:A10) Want to look along row for first participant and if any cell contains "E", display corresponding unit label from cell above. In the row on the report for that participant, I only want to display a list of the 10 units that they are studying. Am flummoxed... any ideas gratfully received

Report abuse

Reply

11 years ago

by

Uncle

England, United Kingdom

Joined 19 years ago

Hi,

You can use an array to loop through and pick up the values and then write them to the next sheet:

Sub GetUnits()

Dim astrUnits(10) As String 'array to hold names and units
Dim intCount As Integer     'to keep track of place in array
Dim i As Integer    'loop counter
Dim j As Integer
Dim k As Integer

For j = 2 To 10
'set counter for astrUnits and student
intCount = 1
'get student name
    astrUnits(0) = Cells(j, 1).Value
    For i = 2 To 17
        'get units picked
        If Cells(j, i).Value = "E" Then
            'add to array and increment counter
            astrUnits(intCount) = Cells(1, i).Value
            intCount = intCount + 1
        End If
    Next i
    'now add to report sheet
    ActiveSheet.Next.Select
        For k = 0 To 10
            Cells(j - 1, k + 1).Value = astrUnits(k)
        Next k
    ActiveSheet.Previous.Select

Next j

End Sub

Hope this helps!

Report abuse