Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Unanswered: Vb iN microsoft excel

    I'm farely new to vb. I'm trying to write some code in an Excel spreed sheet for a staffing model. Here is my scenario. I have 4 columns: rank, hire ability, hires and number hired. There are for sites. I'll call them blue,red, green and yellow. I want put a number in hires and then have the sheet calculate based on rank. The hirability determines the number of staff that can be hired in each site. I want hire 100 and the hire ability for each site is 25 then the number hired for each site would be 25. I need it to place the number available in rank 1 and then loop through the other 3. I would need a remainder box if after all sites are filled there are still some left.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Is this the sort of thing you are after

    Code:
    Sub Test()
        Dim LastRow As Integer
        Dim Hires As Integer, I As Integer
        Dim colRank As Range
        Dim rngFindRank As Range
        
        'Pick up Number of Hires Wanted
        'in my example value is in cell G1
        If Not Range("G1").Value = "" Then
            Hires = Range("G1").Value
        Else
            MsgBox "Please insert number of Hires into Cell G1"
            Exit Sub
        End If
        
        'set up the Rank Column (A)
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Set colRank = Range("A2:A" & LastRow)
        
        
        'Loop through each rank and assign Hires
        For I = 1 To LastRow - 1 '-1 as rank starts in cell 2
            'Find Lowest Rank
            Set rngFindRank = colRank.Find(I)
            If Not rngFindRank Is Nothing Then
                'check to see if total can be filled
                If rngFindRank.Offset(0, 1).Value <= Hires Then
                    'case when more hires than hore ability
                    rngFindRank.Offset(0, 2).Value = rngFindRank.Offset(0, 1).Value
                    Hires = Hires - rngFindRank.Offset(0, 1).Value
                Else
                    rngFindRank.Offset(0, 2).Value = Hires
                    Hires = 0
                End If
            End If
            'clear object Variable
            Set rngFindRank = Nothing
        Next I
        
        'put remainders into remainder box
        'in this example cell G2
        Range("G2").Value = Hires
            
    End Sub
    Dave

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    Thanks so much for your help

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    No problem if you have any problems please just post back and ill try to help

Posting Permissions

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