Community discussion forum

Help with VBA Excel code

• • Reply

  2 months ago

  by KDHTSVAU

  Kevin Hubbard

  Townsville, Australia

  Joined 2 months 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

  Post was edited on 21/09/2009 09:37:56 Report abuse
• • Reply

  2 months ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 7 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
• • Reply

  1 month ago

  by KDHTSVAU

  Kevin Hubbard

  Townsville, Australia

  Joined 2 months ago

  Beauty! Works a charm Many thanks

  Report abuse