Help with VBA Excel code

code , excel 2003 , vba Townsville, Australia
  • 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

  • 11 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!

  • 11 years ago

    Beauty! Works a charm Many thanks

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An idiot with a computer is a faster, better idiot” - Rich Julius