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

VictoriaExcel

New Member
Joined
Nov 15, 2018
Messages
14
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. :confused:

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,

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

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hello Victoria,

Out of your 295 seats listed ... is it on purpose ... some seats seem left out : A15,G15,H15, etc ...
 
Upvote 0
Hi
List your seat numbers in col Z (295 seats). paste the following codes in the macro window.
Code:
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
 
Upvote 0
Hello,

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

HTH
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
Hello,

Could you post the macro you are already using to filter your sheet ... :wink:
 
Upvote 0
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
 
Upvote 0
Hello,

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

You could use the following formula :

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

Hope this will help
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top