If Statements and dealing with blank cells

[terrykalaka]

Hey Gang,

Long time lurker, first time poster here! I've searched around a for a couple of days to try to figure out my deal here, but haven't been able to find a solution, so here goes.

The problem I'm working on is for creating start lists for a cycling event. We start with a Sign In worksheet like this:

	A	B	C	D	E	F	G	H
1		No.	Name	Lic #	Cat.	State	TT	IP
2	1	96	John Stevenson	58245	1	GA	x	x
3	2	68	Steven James	23654	3	GA		x
4	3	10	Don Johnson	44038	4	GA	x
5	4	32	Chad Christie	64573	2	GA	x	x

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

The TT and IP columns are the two different events, x in each of columns G and H column for riders signing up for that particular event. From that, we create a start/time sheet using formula:

=IF('Sign In'!G2="","",'Sign In'!C2)

To get this Start List worksheet:

	A	B	C	D	E
1	Heat	Start Position	No.	Men's 1000m TT	Time
2	1	Front	96	John Stevenson
3		Back
4	2	Front	10	Don Johnson
5		Back	32	Chad Christie
6
7	Heat	Start Position	No.	Men's 4k IP	Time
8	1	Front	96	John Stevenson
9		Back	68	Steven James
10	2	Front
11		Back	32	Chad Christie

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

Everything works great on that part, except for the blank cells that are left from riders in the original Sign In sheet not doing a particular event. Right now, we manually go through all of the lists and remove the blank cells. This is where I'd like to automate that process, so that in the case that the IF statement returned a blank cell, that it would read the next cell from the Sign In sheet until it returned an actual value. What do you guys think?

 

[terrykalaka]

Ha yeah, looks crazy, so I guess I'm going to start learning VBA now. I could pick your brain all the way, but I'll need to get there myself. Got a few things going like dictating a range using currRow instead of using the lastrow reference. A couple of questions to get me moving along though:

A way for the heat number to increase by one each time it's displayed? Right now it only shows heat one for all the heats.

On the format front, where would I slot in code to change the font size of the header cells:
Cells(1, 1) = "Heat"
Cells(1, 2) = "Start"
Cells(1, 3) = "No."
Cells(1, 4) = "Masters 50+ 500m TT"
Cells(1, 5) = "Time"



Update before I post:
Had this reply open for a while. Cooking with gas in general though, getting it going great so far, apart from the above couple of things.

 

[par60056]

the lastrow reference looks at the sign in sheet and finds the row with the last entry in column C. It starts at row 1000 and looks up until there is a non blank cell. As long as you don't have more than 1000 signed up you are ok. If you expect more than 1000 change the value C1000 to C64000 or bigger. I assumed a fairly small racing group.

I tend to fill in values 1st then format them. Set column widths before changing formatting because some formatting will cause the row height to be huge.

Use of the "With" statement is a shortcut to save a lot of typing.

So something like this:

Cells(1, 1) = "Heat"
Cells(1, 2) = "Start Position"
Cells(1, 3) = "No."
Cells(1, 4) = "Men's 1000m TT"
Cells(1, 5) = "Time"
Columns(1).ColumnWidth = 9
Columns(2).ColumnWidth = 12
Columns(3).ColumnWidth = 8
Columns(4).ColumnWidth = 14
Columns(5).ColumnWidth = 9


With Range(Cells(1, 1), Cells(1, 5))
    .Font.Bold = True
    .Font.Size = 14
    .WrapText = True
    .HorizontalAlignment = xlCenter
End With

.

 

[terrykalaka]

So things are working pretty well so far, I've hit a couple of snags that I can't quite figure out:

Increasing the heat number by one for each new heat, see below picture, the heat numbers within a race should increase by 1 each time it's displayed. So Masters 50+ should should heat 1, Heat 2, Heat 3. Then womens start back with Heat 1, Heat 2, Heat 3 so on. Tried heat + 1, but the they're all just 2, tried some different configurations of something like this: Cells(2, 1).Value + 1 but it only looks at the absolute cell (2, 1), I can't figure out how to make it look 2 cells above it instead?

And the Front/Back part is only working for even/odd row numbers, so when is a race has an uneven number of participants, the next race starts with Back instead of Front, see Womens 500m TT below. This could be overcome by adding a blank Back row or even just a extra plain blank row to any race that that has a uneven number of riders.
I tried to give that a go, to no avail by replacing
Next
outRow = outRow + 2

with

Next
If outRow Mod 2 = 0 Then
outRow = outRow + 3
Else
outRow = outRow + 2
End If

But nothing changed for me
Edit: figured this part out with this code:
Next
If outRow Mod 2 = 1 Then
Cells(outRow, 2) = "Back"
outRow = outRow + 3
Else
outRow = outRow + 2
End If


-- removed inline image ---


Currently, code is looking like this:

Sub startlist()
Dim currRow As Long
Dim heat As Integer
Dim outRow As Long


Application.ScreenUpdating = False
Cells(1, 1) = "Heat"
Cells(1, 2) = "Start"
Cells(1, 3) = "No."
Cells(1, 4) = "Masters 50+ 500m TT"
Cells(1, 5) = "Time"
Columns(1).ColumnWidth = 6
Columns(2).ColumnWidth = 8
Columns(3).ColumnWidth = 6
Columns(4).ColumnWidth = 35
Columns(5).ColumnWidth = 25
Rows("1:200").RowHeight = 22
Cells(1, 1).Font.Size = 10
Cells(1, 2).Font.Size = 10
Cells(1, 3).Font.Size = 10
Cells(1, 4).Font.Size = 18
Cells(1, 5).Font.Size = 10

outRow = 2
heat = Cells(2, 1).Value + 1

For currRow = 66 To 77
    If (Sheets("Sign In").Cells(currRow, 7) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = Cells(2, 1).Value + 1
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        End If
        outRow = outRow + 1
    End If
Next
If outRow Mod 2 = 1 Then
    Cells(outRow, 2) = "Back"
    outRow = outRow + 3
Else
    outRow = outRow + 2
End If
Cells(outRow - 1, 1) = "Heat"
Cells(outRow - 1, 2) = "Start"
Cells(outRow - 1, 3) = "No."
Cells(outRow - 1, 4) = "Womens 500m TT"
Cells(outRow - 1, 5) = "Time"
Cells(outRow - 1, 1).Font.Size = 10
Cells(outRow - 1, 2).Font.Size = 10
Cells(outRow - 1, 3).Font.Size = 10
Cells(outRow - 1, 4).Font.Size = 18
Cells(outRow - 1, 5).Font.Size = 10
heat = 1
For currRow = 34 To 45
    If (Sheets("Sign In").Cells(currRow, 7) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = Cells(2, 1).Value + 1
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        End If
        outRow = outRow + 1
    End If
Next

 

[terrykalaka]

ha guess putting the image in there didn't work out to well!

 

[par60056]

the code you inserted is between the 2 race types.

I understand it. because of the row mod it needs to start the other race on the right row.

I inserted a couple lines in red (I hope)

Dim currRow As Long
Dim heat As Integer
Dim outRow As Long




Application.ScreenUpdating = False
Cells(1, 1) = "Heat"
Cells(1, 2) = "Start"
Cells(1, 3) = "No."
Cells(1, 4) = "Masters 50+ 500m TT"
Cells(1, 5) = "Time"
Columns(1).ColumnWidth = 6
Columns(2).ColumnWidth = 8
Columns(3).ColumnWidth = 6
Columns(4).ColumnWidth = 35
Columns(5).ColumnWidth = 25
Rows("1:200").RowHeight = 22
Cells(1, 1).Font.Size = 10
Cells(1, 2).Font.Size = 10
Cells(1, 3).Font.Size = 10
Cells(1, 4).Font.Size = 18
Cells(1, 5).Font.Size = 10


outRow = 2
heat = Cells(2, 1).Value + 1


For currRow = 66 To 77
    If (Sheets("Sign In").Cells(currRow, 7) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = Cells(2, 1).Value + 1
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
[COLOR=#ff0000]            heat = heat + 1[/COLOR]
        End If
        outRow = outRow + 1
    End If
Next
If outRow Mod 2 = 1 Then
    Cells(outRow, 2) = "Back"
    outRow = outRow + 3
Else
    outRow = outRow + 2
End If
heat = 1
Cells(outRow - 1, 1) = "Heat"
Cells(outRow - 1, 2) = "Start"
Cells(outRow - 1, 3) = "No."
Cells(outRow - 1, 4) = "Womens 500m TT"
Cells(outRow - 1, 5) = "Time"
Cells(outRow - 1, 1).Font.Size = 10
Cells(outRow - 1, 2).Font.Size = 10
Cells(outRow - 1, 3).Font.Size = 10
Cells(outRow - 1, 4).Font.Size = 18
Cells(outRow - 1, 5).Font.Size = 10
[COLOR=#ff0000]heat = 1[/COLOR]
For currRow = 34 To 45
    If (Sheets("Sign In").Cells(currRow, 7) <> "") Then
        If outRow Mod 2 = 0 Then
            Cells(outRow, 1) = Cells(2, 1).Value + 1
            Cells(outRow, 2) = "Front"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
        Else
            Cells(outRow, 2) = "Back"
            Cells(outRow, 3) = Sheets("Sign In").Cells(currRow, 2)
            Cells(outRow, 4) = Sheets("Sign In").Cells(currRow, 3)
[COLOR=#ff0000]            heat = heat + 1[/COLOR]
        End If
        outRow = outRow + 1
    End If
Next

 

[terrykalaka]

Awesome, that nailed it, I had been tried to add heat + 1 too early in the whole deal to make it work right, thanks a bunch for your help!