Still haven't figured this one out

[mevetts]

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.

[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

--
Don Guillett
SalesAid Software
[email protected]
"mevetts" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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.

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

[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 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

[Don Guillett]

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

Next
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"mevetts" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>