Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arbitrary lookups in VBA (2000)

    Hello,

    I am making a spreadsheet to help schedule a particular job at work. I am trying to edit the caption of a label to show the names of everyone unavailable for that day.

    Right now, I have this process started. The label is able to get the number of people unavailable for each day and insert a new line for each person. Instead of diplaying "Name x" I want it to display the names of the people unavailable for each day on a new line in the label.

    If there is a 'tentative' listed for that particular date, I don't want that name displayed. This is a step that can be put off until the basic problem is working.

    I'm pretty confident that I could use a series of array formulas (index-small method) within the worksheet to find the names I'm looking for, but array formulas have a knack for slowing down the performance of the workbook (The original already has some calculation speed issues). Also, if this is done entirely within VBA the updates can be controlled easier.

    The buttons will be the users method of transversing through the dates, but a new date can be typed in instead (it just won't update until a button is pushed).

    If I was unclear about the problem or what I would like, let me know.

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Arbitrary lookups in VBA (2000)

    How about this?

    Steve

    <pre>Private Sub refresh_label()
    Dim lineHeight As Integer
    Dim rng As Range
    Dim rCell As Range
    Dim dDate As Date
    Dim sUnavailable as String

    lineHeight = 13
    sUnavailable = "Can't work"
    dDate = Range("l5").Value

    Set rng = Range(Range("l2"), Range("l65536").End(xlUp))


    Label1.Caption = Range("L5").Value & " Unavailables"
    Label1.Height = lineHeight

    'Put names of unavailable personel for particular date
    'in label


    For Each rCell In rng
    If rCell.Value = dDate And _
    rCell.Offset(0, 1).Value = sUnavailable Then
    Label1.Caption = Label1.Caption & vbCrLf & _
    rCell.Offset(0, -1).Value
    Label1.Height = Label1.Height + lineHeight
    End If
    Next
    End Sub</pre>


  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arbitrary lookups in VBA (2000)

    Works great, thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •