hi,
i have the following code for doing a procedure......i was hoping i could ask for a little help regarding modifying it a little....
First to tell you what the code does......
Right now, it searches through column AA; searches for the lowest number; if it finds the cell with the lowest value in AA, it then adds a 1 to the row to the left of it....the code then subtracts 1 from G39 and does the same process again.....until G39 has a value of zero
Now the problem is that before, i used to have the sections in AA merged....so what you had was ONE 131, ONE 26 and ONE 139
Now what i've done is that i've expanded those merged cells and so now 131 shows on every cell whithin that section (note that in the example i'm posting, i have 3 sections.....each section has the same number)
How do i modify this code so that it searches AA but considers 131 only once as oppose to considering it as many rows as there are?
i have the following code for doing a procedure......i was hoping i could ask for a little help regarding modifying it a little....
First to tell you what the code does......
Right now, it searches through column AA; searches for the lowest number; if it finds the cell with the lowest value in AA, it then adds a 1 to the row to the left of it....the code then subtracts 1 from G39 and does the same process again.....until G39 has a value of zero
Now the problem is that before, i used to have the sections in AA merged....so what you had was ONE 131, ONE 26 and ONE 139
Now what i've done is that i've expanded those merged cells and so now 131 shows on every cell whithin that section (note that in the example i'm posting, i have 3 sections.....each section has the same number)
How do i modify this code so that it searches AA but considers 131 only once as oppose to considering it as many rows as there are?
| non-Working v7 1-28-05.xls | ||||||
|---|---|---|---|---|---|---|
| Z | AA | AB | AC | |||
| 1372 | ||||||
| 1373 | 131 | |||||
| 1374 | 131 | |||||
| 1375 | 131 | |||||
| 1376 | 131 | |||||
| 1377 | 131 | |||||
| 1378 | 131 | |||||
| 1379 | 131 | |||||
| 1380 | 131 | |||||
| 1381 | 131 | |||||
| 1382 | 131 | |||||
| 1383 | ||||||
| 1384 | 36 | |||||
| 1385 | 36 | |||||
| 1386 | 36 | |||||
| 1387 | 36 | |||||
| 1388 | 36 | |||||
| 1389 | 36 | |||||
| 1390 | 36 | |||||
| 1391 | 36 | |||||
| 1392 | ||||||
| 1393 | 139 | |||||
| 1394 | 139 | |||||
| 1395 | 139 | |||||
| 1396 | 139 | |||||
| 1397 | 139 | |||||
| 1398 | 139 | |||||
Sheet1 | ||||||
Code:
Sub evenout(rngaddone As Range)
Dim rng As Range, x&, cell As Range
'***************************************************
'ADDONE IS THE RANGE IN COLUMN AA
Set addone = rngaddone.Offset(0, 17).Cells(1)
'MsgBox Range((addone.Address)).Value
'Exit Sub
'***************************************************
If Not IsNumeric([G39]) Or [G39] = 0 Or Len([G39]) = 0 Then Exit Sub
Set rng = Range([AA1], [AA65536].End(xlUp))
If [G39] > Application.WorksheetFunction.Count(rng) Then
For Each cell In rng
If IsNumeric(cell) And Len(cell)<> 0 Then
[G39] = [G39] - 1
cell.Offset(0, -1) = cell.Offset(0, -1) + 1
End If
Next
Else
x = Application.WorksheetFunction.Small([aa:aa], [G39])
For Each cell In rng
If IsNumeric(cell) And Len(cell)<> 0 And cell<= x Then
[G39] = [G39] - 1
cell.Offset(0, -1) = cell.Offset(0, -1) + 1
If [G39] = 0 Then Exit For
End If
Next
End If
End Sub
