Hi,

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

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

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

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

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

The macro needs to see if there's a number in column A and if there is
then put a tick in that row. It needs to work down the column putting
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 I
mean and give a clearer idea as to what layout I'm working with. So the
date will go in the row where the active cell is currently located and
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 me
completing a significant section of this workbook.

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

Many thanks,

Mark.


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

--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

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

--
Don Guillett
SalesAid Software

"mevetts" wrote in
message ...

Hi,

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

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

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

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

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

The macro needs to see if there's a number in column A and if there is
then put a tick in that row. It needs to work down the column putting
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 I
mean and give a clearer idea as to what layout I'm working with. So the
date will go in the row where the active cell is currently located and
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 me
completing a significant section of this workbook.

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

Many thanks,

Mark.


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

--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

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.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

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.

mevetts wrote:

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.

--
mevetts

------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

--

Dave Peterson

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

Next
End Sub


--
Don Guillett
SalesAid Software

"mevetts" wrote in
message ...

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.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

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.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

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 wrote:

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.

--
mevetts

------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252

--

Dave Peterson

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.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=497252