Still haven't figured this one out

[mevetts]

Hi,

To set the scene, I'm developing a excel based register for m
classes.

I currently have a button that inserts the date and moves down to th
next row.

I would like to develop this code so that it not only puts the date i
but, also inserts a tick symbol next to each of the pupils in tha
class.

I have all my classes on one worksheet, with hyperlinks to navigate t
each of the groups.

Each class will have a different number of pupils, but next to eac
pupil is a number beginning at one and then going up depending on ho
many pupils in the class.

The macro needs to see if there's a number in column A and if there i
then put a tick in that row. It needs to work down the column puttin
the ticks in, but then stop at the end of the list.

I have attached a screen shot of just one of the classes to show what
mean and give a clearer idea as to what layout I'm working with. So th
date will go in the row where the active cell is currently located an
the ticks need to be inserted next to each pupil below the date.

Here's the code I have so far -

Public Sub Date_Today()
With ActiveCell
.Value = Date
.NumberFormat = "dd-mmm-yy"
ActiveCell.Offset(1, 0).Select
End With
End Sub

Any help would be just great, as this would be a big step towards m
completing a significant section of this workbook.

If I haven't explained anything well or you just don't get what I'
going on about then let me know.

Many thanks,

Mark

+-------------------------------------------------------------------
|Filename: screen.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4170
+-------------------------------------------------------------------

 

[Don Guillett]

try this to put a check mark in the next column of each cell that has a
number

Sub puttick()
For Each c In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row)
If Len(c) > 0 And IsNumeric(c) Then
With c.Offset(, 1)
..Value = "a"
..Font.Name = "Marlett"
End With
End If

 

[mevetts]

Hi,

I pasted the code into a new mudule. When I ran it the debugger popped
up saying 'Compile Error: Expected End Sub'

So I add end sub to the end of the code. But when I ran the macro again
it said - 'Compile Error: For without Next' and the end sub line was
highlighted.

Could you help me out?

Thanks,

Mark.

 

[Dave Peterson]

Sub puttick()
For Each c In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row)
If Len(c) > 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = "a"
.Font.Name = "Marlett"
End With
End If
next c
end sub

Sometimes when you indent the code, you can see the missing pieces easier.

 

[Don Guillett]

two more lines didn't get pasted. See Dave's also

Next
End Sub

 

[mevetts]

Hi Dave,

I tried the code out, but it put ticks in column B and didn't stop at
the end of class one, but carried on down all of the other classes
below on the same sheet.

This seems to be proving a very tricky task. I only wish my knowledge
was greater so I could assist you more.

Any other ideas?

Mark.

 

[Dave Peterson]

Don's code stops at the last used cell in column A.

How do you know when class 1 ends?

Maybe just selecting the range in column A, then running the code would be
sufficient?

Sub puttick()
dim C as range
For Each c In Selection.cells
If Len(c) > 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = "a"
.Font.Name = "Marlett"
End With
End If
next c
end sub

Or maybe you can check the value in another cell in that row?

Sub puttick()
Dim c As Range

For Each c In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row)
if lcase(c.offset(0,4).value) <> "class 1" then
exit for
end if
If Len(c) > 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = "a"
.Font.Name = "Marlett"
End With
End If
Next c
End Sub

I used c.offset(0,4). This is 4 columns to the right of column A--or column E.
Adjust that as necessary.

ps. Lots of people connect directly to the MS NewsServers. They don't see your
attachment. (I'm one of those people.)

 

[mevetts]

For anyone that's interested -


Code:
--------------------
Public Sub Date_Today()
Dim LastRow As Long
Dim i As Long

With ActiveCell
LastRow = Range("A" & .Row + 5).End(xlDown).Row
For i = .Row + 5 To LastRow
If Range("A" & i) = "" Then
LastRow = i - 1
Exit For
End If
Next i
.Value = Date
.NumberFormat = "dd-mmm-yy"
Range(Cells(.Row + 5, .Column), Cells(LastRow, .Column)).Font.Name = "Wingdings"
Range(Cells(.Row + 5, .Column), Cells(LastRow, .Column)) = "ü"
End With

End Sub
--------------------


With thanks to the dedication of some Excel gurus on another forum.

Cheers,

Mark.