Assigning sequence of numbers to visible cells only using VBA code (avoid hidden rows)

[VictoriaExcel]

Hello,

I am having difficulty with assigning a specific sequence of numbers (seat numbers listed below) to specific records whilst keeping the order. For context, I have a running order of graduands and those known to be attending are marked as attending in column C as well as the row being highlighted orange by a previous macro conditionally formatting.

I have attempted to record a macro by filtering by colour, selecting only visible cells in column B for the full range of records (Seat No.) and entered a formula linking to the start of the below sequence in a different sheet ='TH Seat Nos.'!A2 but unfortunately because the pattern of those attending/not attending is not consistent the formula pulls in from the below sequence inconsistently.

Therefore, what I am asking is do you know how to assign the below sequence in sheet ='TH Seat Nos.'!A2 in order within column B to only records where column C is a Y.

I need to keep the existing order of the rows and the not attending records as we need the data to hand for slot ins on the day.

Many thanks in advance,

[TABLE="width: 128"]
<colgroup><col></colgroup><tbody>[TR]
[TD]A2[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[/TR]
[TR]
[TD]A11[/TD]
[/TR]
[TR]
[TD]A12[/TD]
[/TR]
[TR]
[TD]A13[/TD]
[/TR]
[TR]
[TD]A14[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[/TR]
[TR]
[TD]B10[/TD]
[/TR]
[TR]
[TD]B11[/TD]
[/TR]
[TR]
[TD]B12[/TD]
[/TR]
[TR]
[TD]B13[/TD]
[/TR]
[TR]
[TD]B14[/TD]
[/TR]
[TR]
[TD]B15[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[/TR]
[TR]
[TD]C5[/TD]
[/TR]
[TR]
[TD]C6[/TD]
[/TR]
[TR]
[TD]C7[/TD]
[/TR]
[TR]
[TD]C8[/TD]
[/TR]
[TR]
[TD]C9[/TD]
[/TR]
[TR]
[TD]C10[/TD]
[/TR]
[TR]
[TD]C11[/TD]
[/TR]
[TR]
[TD]C12[/TD]
[/TR]
[TR]
[TD]C13[/TD]
[/TR]
[TR]
[TD]C14[/TD]
[/TR]
[TR]
[TD]C15[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[/TR]
[TR]
[TD]D5[/TD]
[/TR]
[TR]
[TD]D6[/TD]
[/TR]
[TR]
[TD]D7[/TD]
[/TR]
[TR]
[TD]D8[/TD]
[/TR]
[TR]
[TD]D9[/TD]
[/TR]
[TR]
[TD]D10[/TD]
[/TR]
[TR]
[TD]D11[/TD]
[/TR]
[TR]
[TD]D12[/TD]
[/TR]
[TR]
[TD]D13[/TD]
[/TR]
[TR]
[TD]D14[/TD]
[/TR]
[TR]
[TD]D15[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[/TR]
[TR]
[TD]E5[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[/TR]
[TR]
[TD]E7[/TD]
[/TR]
[TR]
[TD]E8[/TD]
[/TR]
[TR]
[TD]E9[/TD]
[/TR]
[TR]
[TD]E10[/TD]
[/TR]
[TR]
[TD]E11[/TD]
[/TR]
[TR]
[TD]E12[/TD]
[/TR]
[TR]
[TD]E13[/TD]
[/TR]
[TR]
[TD]E14[/TD]
[/TR]
[TR]
[TD]E15[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[/TR]
[TR]
[TD]F3[/TD]
[/TR]
[TR]
[TD]F4[/TD]
[/TR]
[TR]
[TD]F5[/TD]
[/TR]
[TR]
[TD]F6[/TD]
[/TR]
[TR]
[TD]F7[/TD]
[/TR]
[TR]
[TD]F8[/TD]
[/TR]
[TR]
[TD]F9[/TD]
[/TR]
[TR]
[TD]F10[/TD]
[/TR]
[TR]
[TD]F11[/TD]
[/TR]
[TR]
[TD]F12[/TD]
[/TR]
[TR]
[TD]F13[/TD]
[/TR]
[TR]
[TD]F14[/TD]
[/TR]
[TR]
[TD]F15[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[/TR]
[TR]
[TD]G3[/TD]
[/TR]
[TR]
[TD]G4[/TD]
[/TR]
[TR]
[TD]G5[/TD]
[/TR]
[TR]
[TD]G6[/TD]
[/TR]
[TR]
[TD]G7[/TD]
[/TR]
[TR]
[TD]G8[/TD]
[/TR]
[TR]
[TD]G9[/TD]
[/TR]
[TR]
[TD]G10[/TD]
[/TR]
[TR]
[TD]G11[/TD]
[/TR]
[TR]
[TD]G12[/TD]
[/TR]
[TR]
[TD]G13[/TD]
[/TR]
[TR]
[TD]G14[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[/TR]
[TR]
[TD]H3[/TD]
[/TR]
[TR]
[TD]H4[/TD]
[/TR]
[TR]
[TD]H5[/TD]
[/TR]
[TR]
[TD]H6[/TD]
[/TR]
[TR]
[TD]H7[/TD]
[/TR]
[TR]
[TD]H8[/TD]
[/TR]
[TR]
[TD]H9[/TD]
[/TR]
[TR]
[TD]H10[/TD]
[/TR]
[TR]
[TD]H11[/TD]
[/TR]
[TR]
[TD]H12[/TD]
[/TR]
[TR]
[TD]H13[/TD]
[/TR]
[TR]
[TD]H14[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[/TR]
[TR]
[TD]I3[/TD]
[/TR]
[TR]
[TD]I4[/TD]
[/TR]
[TR]
[TD]I5[/TD]
[/TR]
[TR]
[TD]I6[/TD]
[/TR]
[TR]
[TD]I7[/TD]
[/TR]
[TR]
[TD]I8[/TD]
[/TR]
[TR]
[TD]I9[/TD]
[/TR]
[TR]
[TD]I10[/TD]
[/TR]
[TR]
[TD]I11[/TD]
[/TR]
[TR]
[TD]I12[/TD]
[/TR]
[TR]
[TD]I13[/TD]
[/TR]
[TR]
[TD]I14[/TD]
[/TR]
[TR]
[TD]I15[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[/TR]
[TR]
[TD]J4[/TD]
[/TR]
[TR]
[TD]J5[/TD]
[/TR]
[TR]
[TD]J6[/TD]
[/TR]
[TR]
[TD]J7[/TD]
[/TR]
[TR]
[TD]J8[/TD]
[/TR]
[TR]
[TD]J9[/TD]
[/TR]
[TR]
[TD]J10[/TD]
[/TR]
[TR]
[TD]J11[/TD]
[/TR]
[TR]
[TD]J12[/TD]
[/TR]
[TR]
[TD]J13[/TD]
[/TR]
[TR]
[TD]J14[/TD]
[/TR]
[TR]
[TD]J15[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[/TR]
[TR]
[TD]K3[/TD]
[/TR]
[TR]
[TD]K4[/TD]
[/TR]
[TR]
[TD]K5[/TD]
[/TR]
[TR]
[TD]K6[/TD]
[/TR]
[TR]
[TD]K7[/TD]
[/TR]
[TR]
[TD]K8[/TD]
[/TR]
[TR]
[TD]K9[/TD]
[/TR]
[TR]
[TD]K10[/TD]
[/TR]
[TR]
[TD]K11[/TD]
[/TR]
[TR]
[TD]K12[/TD]
[/TR]
[TR]
[TD]K13[/TD]
[/TR]
[TR]
[TD]K14[/TD]
[/TR]
[TR]
[TD]K15[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[/TR]
[TR]
[TD]L3[/TD]
[/TR]
[TR]
[TD]L4[/TD]
[/TR]
[TR]
[TD]L5[/TD]
[/TR]
[TR]
[TD]L6[/TD]
[/TR]
[TR]
[TD]L7[/TD]
[/TR]
[TR]
[TD]L8[/TD]
[/TR]
[TR]
[TD]L9[/TD]
[/TR]
[TR]
[TD]L10[/TD]
[/TR]
[TR]
[TD]L11[/TD]
[/TR]
[TR]
[TD]L12[/TD]
[/TR]
[TR]
[TD]L13[/TD]
[/TR]
[TR]
[TD]L14[/TD]
[/TR]
[TR]
[TD]L15[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[/TR]
[TR]
[TD]M7[/TD]
[/TR]
[TR]
[TD]M8[/TD]
[/TR]
[TR]
[TD]M9[/TD]
[/TR]
[TR]
[TD]M10[/TD]
[/TR]
[TR]
[TD]M11[/TD]
[/TR]
[TR]
[TD]M12[/TD]
[/TR]
[TR]
[TD]M13[/TD]
[/TR]
[TR]
[TD]M14[/TD]
[/TR]
[TR]
[TD]M15[/TD]
[/TR]
[TR]
[TD]N2[/TD]
[/TR]
[TR]
[TD]N3[/TD]
[/TR]
[TR]
[TD]N4[/TD]
[/TR]
[TR]
[TD]N5[/TD]
[/TR]
[TR]
[TD]N6[/TD]
[/TR]
[TR]
[TD]N7[/TD]
[/TR]
[TR]
[TD]N8[/TD]
[/TR]
[TR]
[TD]N9[/TD]
[/TR]
[TR]
[TD]N10[/TD]
[/TR]
[TR]
[TD]N11[/TD]
[/TR]
[TR]
[TD]N12[/TD]
[/TR]
[TR]
[TD]N13[/TD]
[/TR]
[TR]
[TD]N14[/TD]
[/TR]
[TR]
[TD]N15[/TD]
[/TR]
[TR]
[TD]O2[/TD]
[/TR]
[TR]
[TD]O3[/TD]
[/TR]
[TR]
[TD]O4[/TD]
[/TR]
[TR]
[TD]O5[/TD]
[/TR]
[TR]
[TD]O6[/TD]
[/TR]
[TR]
[TD]O7[/TD]
[/TR]
[TR]
[TD]O8[/TD]
[/TR]
[TR]
[TD]O9[/TD]
[/TR]
[TR]
[TD]O10[/TD]
[/TR]
[TR]
[TD]O11[/TD]
[/TR]
[TR]
[TD]O12[/TD]
[/TR]
[TR]
[TD]O13[/TD]
[/TR]
[TR]
[TD]O14[/TD]
[/TR]
[TR]
[TD]O15[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[/TR]
[TR]
[TD]P4[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[/TR]
[TR]
[TD]P6[/TD]
[/TR]
[TR]
[TD]P7[/TD]
[/TR]
[TR]
[TD]P8[/TD]
[/TR]
[TR]
[TD]P9[/TD]
[/TR]
[TR]
[TD]P10[/TD]
[/TR]
[TR]
[TD]P11[/TD]
[/TR]
[TR]
[TD]P12[/TD]
[/TR]
[TR]
[TD]P13[/TD]
[/TR]
[TR]
[TD]P14[/TD]
[/TR]
[TR]
[TD]P15[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]Q5[/TD]
[/TR]
[TR]
[TD]Q6[/TD]
[/TR]
[TR]
[TD]Q7[/TD]
[/TR]
[TR]
[TD]Q8[/TD]
[/TR]
[TR]
[TD]Q9[/TD]
[/TR]
[TR]
[TD]Q10[/TD]
[/TR]
[TR]
[TD]Q11[/TD]
[/TR]
[TR]
[TD]Q12[/TD]
[/TR]
[TR]
[TD]Q13[/TD]
[/TR]
[TR]
[TD]Q14[/TD]
[/TR]
[TR]
[TD]Q15[/TD]
[/TR]
[TR]
[TD]R2[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[/TR]
[TR]
[TD]R4[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[/TR]
[TR]
[TD]R11[/TD]
[/TR]
[TR]
[TD]R12[/TD]
[/TR]
[TR]
[TD]R13[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[/TR]
[TR]
[TD]S4[/TD]
[/TR]
[TR]
[TD]S5[/TD]
[/TR]
[TR]
[TD]S6[/TD]
[/TR]
[TR]
[TD]S7[/TD]
[/TR]
[TR]
[TD]S8[/TD]
[/TR]
[TR]
[TD]S9[/TD]
[/TR]
[TR]
[TD]S10[/TD]
[/TR]
[TR]
[TD]S11[/TD]
[/TR]
[TR]
[TD]S12[/TD]
[/TR]
[TR]
[TD]S13[/TD]
[/TR]
[TR]
[TD]T2[/TD]
[/TR]
[TR]
[TD]T3[/TD]
[/TR]
[TR]
[TD]T4[/TD]
[/TR]
[TR]
[TD]T5[/TD]
[/TR]
[TR]
[TD]T6[/TD]
[/TR]
[TR]
[TD]T7[/TD]
[/TR]
[TR]
[TD]T8[/TD]
[/TR]
[TR]
[TD]T9[/TD]
[/TR]
[TR]
[TD]T10[/TD]
[/TR]
[TR]
[TD]T11[/TD]
[/TR]
[TR]
[TD]T12[/TD]
[/TR]
[TR]
[TD]U2[/TD]
[/TR]
[TR]
[TD]U3[/TD]
[/TR]
[TR]
[TD]U4[/TD]
[/TR]
[TR]
[TD]U5[/TD]
[/TR]
[TR]
[TD]U6[/TD]
[/TR]
[TR]
[TD]U7[/TD]
[/TR]
[TR]
[TD]U8[/TD]
[/TR]
[TR]
[TD]U9[/TD]
[/TR]
[TR]
[TD]U10[/TD]
[/TR]
[TR]
[TD]V2[/TD]
[/TR]
[TR]
[TD]V3[/TD]
[/TR]
[TR]
[TD]V4[/TD]
[/TR]
[TR]
[TD]V5[/TD]
[/TR]
[TR]
[TD]V6[/TD]
[/TR]
[TR]
[TD]V7[/TD]
[/TR]
[TR]
[TD]V8[/TD]
[/TR]
[TR]
[TD]V9[/TD]
[/TR]
[TR]
[TD]W2[/TD]
[/TR]
[TR]
[TD]W3[/TD]
[/TR]
[TR]
[TD]W4[/TD]
[/TR]
[TR]
[TD]W5[/TD]
[/TR]
[TR]
[TD]W6[/TD]
[/TR]
[TR]
[TD]W7[/TD]
[/TR]
[TR]
[TD]W8[/TD]
[/TR]
[TR]
[TD]W9[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello Victoria,

Out of your 295 seats listed ... is it on purpose ... some seats seem left out : A15,G15,H15, etc ...

 

[ravishankar]

Hi
List your seat numbers in col Z (295 seats). paste the following codes in the macro window.

Sub victoria()
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer
a = Cells(Rows.Count, 3).End(xlUp).Row
c = Cells(Rows.Count, 26).End(xlUp).Row
b = Application.WorksheetFunction.CountIf("C2:C" & a, "Y")
e = 1
        If b > c Then
        MsgBox "there are more students than the seats listed"
        Else
        End If
    For d = 2 To a
        If Cells(d, 3) = "Y" Then
        Cells(d, 25) = Cells(e, 26)
        e = e + 1
        End If
    Next d
MsgBox "complete"
End Sub

run the macro. it will assign seat numbers in col Y if there is a Y in col C. try it on a copy of your excel file .
Ravishankar

 

[James006]

Hello,

Ideally your numbering process should be included within your existing macro which performs the filtering by color ....

HTH

 

[VictoriaExcel]

Hi,

Yes the seating sequence needs to be as described as those missing seats/spaces need to be reserved for various reasons and thus not allocated to attending students.

Thank you for the code! Unfortunately it isn't working yet. I received a compile error message stating type mismatch which I have highlighted in blue below:

Sub victoria()
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer
a = Cells(Rows.Count, 3).End(xlUp).Row
c = Cells(Rows.Count, 26).End(xlUp).Row
b = Application.WorksheetFunction.CountIf("C2:C" & a, "Y")
e = 1
If b > c Then
MsgBox "there are more students than the seats listed"
Else
End If
For d = 2 To a
If Cells(d, 3) = "Y" Then
Cells(d, 25) = Cells(e, 26)
e = e + 1
End If
Next d
MsgBox "complete"
End Sub
If this could work alongside the macro that highlights the row that would be great. At the moment I have recorded conditional formatting to cover this task.

Many thanks,

Victoria

 

[VictoriaExcel]

Thank you for the code!

Unfortunately it isn't working just yet. I received a compile error message stating type mismatch on the &

b = Application.WorksheetFunction.CountIf("C2:C" & a, "Y")

 

[James006]

Hello,

Could you post the macro you are already using to filter your sheet ... :wink:

 

[VictoriaExcel]

Hello,

The filtering method didn't work unfortunately and was a recorded macro, therefore I have removed the filters on the page.

I need to assign a Seat No in column B according to the existing sequence of seat numbers* if column C is a "Y" for attending. Rows not marked as Y for attending need to not interrupt the sequence.

*The seating sequence is in another sheet but can be moved to wherever necessary for the purpose of the macro.

I hope that makes more sense.

Many thanks in advance,

Victoria

 

[James006]

Hello,

Say you have stored your list of 295 seats in the range Z2:Z296 ...

You could use the following formula :

=IF(C2="","",INDEX($Z$2:$Z$296,COUNTIF($C$2:C2,"Y")))

Hope this will help

 

[VictoriaExcel]

Thank you very much for the Index suggestion it works perfectly I'd tried the count if alone and it didn't work so I then over complicated!