# Thread: Vb iN microsoft excel

1. Registered User
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. Registered User
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. Registered User
Join Date
Aug 2004
Posts
8
Thanks so much for your help

4. Registered User
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
•