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 = quot;dd-mmm-yyquot;
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: www.excelforum.com/attachment.php?postid=4170 |
-------------------------------------------------------------------
--
mevetts------------------------------------------------------------------------
mevetts's Profile: www.excelforum.com/member.php...oamp;userid=29130
View this thread: www.excelforum.com/showthread...hreadid=497252try this to put a check mark in the next column of each cell that has a
number
Sub puttick()
For Each c In Range(quot;a2:aquot; amp; Cells(Rows.Count, quot;aquot;).End(xlUp).Row)
If Len(c) gt; 0 And IsNumeric(c) Then
With c.Offset(, 1)
..Value = quot;aquot;
..Font.Name = quot;Marlettquot;
End With
End If
--
Don Guillett
SalesAid Software
quot;mevettsquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; To set the scene, I'm developing a excel based register for my
gt; classes.
gt;
gt; I currently have a button that inserts the date and moves down to the
gt; next row.
gt;
gt; I would like to develop this code so that it not only puts the date in
gt; but, also inserts a tick symbol next to each of the pupils in that
gt; class.
gt;
gt; I have all my classes on one worksheet, with hyperlinks to navigate to
gt; each of the groups.
gt;
gt; Each class will have a different number of pupils, but next to each
gt; pupil is a number beginning at one and then going up depending on how
gt; many pupils in the class.
gt;
gt; The macro needs to see if there's a number in column A and if there is
gt; then put a tick in that row. It needs to work down the column putting
gt; the ticks in, but then stop at the end of the list.
gt;
gt; I have attached a screen shot of just one of the classes to show what I
gt; mean and give a clearer idea as to what layout I'm working with. So the
gt; date will go in the row where the active cell is currently located and
gt; the ticks need to be inserted next to each pupil below the date.
gt;
gt; Here's the code I have so far -
gt;
gt; Public Sub Date_Today()
gt; With ActiveCell
gt; Value = Date
gt; NumberFormat = quot;dd-mmm-yyquot;
gt; ActiveCell.Offset(1, 0).Select
gt; End With
gt; End Sub
gt;
gt; Any help would be just great, as this would be a big step towards me
gt; completing a significant section of this workbook.
gt;
gt; If I haven't explained anything well or you just don't get what I'm
gt; going on about then let me know.
gt;
gt; Many thanks,
gt;
gt; Mark.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: screen.jpg |
gt; |Download: www.excelforum.com/attachment.php?postid=4170 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; mevetts
gt;
gt;
gt; ------------------------------------------------------------------------
gt; mevetts's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29130
gt; View this thread: www.excelforum.com/showthread...hreadid=497252
gt;
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: www.excelforum.com/member.php...oamp;userid=29130
View this thread: www.excelforum.com/showthread...hreadid=497252Sub puttick()
For Each c In Range(quot;a2:aquot; amp; Cells(Rows.Count, quot;aquot;).End(xlUp).Row)
If Len(c) gt; 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = quot;aquot;
.Font.Name = quot;Marlettquot;
End With
End If
next c
end sub
Sometimes when you indent the code, you can see the missing pieces easier.
mevetts wrote:
gt;
gt; Hi,
gt;
gt; I pasted the code into a new mudule. When I ran it the debugger popped
gt; up saying 'Compile Error: Expected End Sub'
gt;
gt; So I add end sub to the end of the code. But when I ran the macro again
gt; it said - 'Compile Error: For without Next' and the end sub line was
gt; highlighted.
gt;
gt; Could you help me out?
gt;
gt; Thanks,
gt;
gt; Mark.
gt;
gt; --
gt; mevetts
gt;
gt; ------------------------------------------------------------------------
gt; mevetts's Profile: www.excelforum.com/member.php...oamp;userid=29130
gt; View this thread: 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
quot;mevettsquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; I pasted the code into a new mudule. When I ran it the debugger popped
gt; up saying 'Compile Error: Expected End Sub'
gt;
gt; So I add end sub to the end of the code. But when I ran the macro again
gt; it said - 'Compile Error: For without Next' and the end sub line was
gt; highlighted.
gt;
gt; Could you help me out?
gt;
gt; Thanks,
gt;
gt; Mark.
gt;
gt;
gt; --
gt; mevetts
gt;
gt;
gt; ------------------------------------------------------------------------
gt; mevetts's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29130
gt; View this thread: www.excelforum.com/showthread...hreadid=497252
gt;
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: www.excelforum.com/member.php...oamp;userid=29130
View this thread: www.excelforum.com/showthread...hreadid=497252Don'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) gt; 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = quot;aquot;
.Font.Name = quot;Marlettquot;
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(quot;a2:aquot; amp; Cells(Rows.Count, quot;aquot;).End(xlUp).Row)
if lcase(c.offset(0,4).value) lt;gt; quot;class 1quot; then
exit for
end if
If Len(c) gt; 0 And IsNumeric(c) Then
With c.Offset(, 1)
.Value = quot;aquot;
.Font.Name = quot;Marlettquot;
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:
gt;
gt; Hi Dave,
gt;
gt; I tried the code out, but it put ticks in column B and didn't stop at
gt; the end of class one, but carried on down all of the other classes
gt; below on the same sheet.
gt;
gt; This seems to be proving a very tricky task. I only wish my knowledge
gt; was greater so I could assist you more.
gt;
gt; Any other ideas?
gt;
gt; Mark.
gt;
gt; --
gt; mevetts
gt;
gt; ------------------------------------------------------------------------
gt; mevetts's Profile: www.excelforum.com/member.php...oamp;userid=29130
gt; View this thread: 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(quot;Aquot; amp; .Row 5).End(xlDown).Row
For i = .Row 5 To LastRow
If Range(quot;Aquot; amp; i) = quot;quot; Then
LastRow = i - 1
Exit For
End If
Next i
.Value = Date
.NumberFormat = quot;dd-mmm-yyquot;
Range(Cells(.Row 5, .Column), Cells(LastRow, .Column)).Font.Name = quot;Wingdingsquot;
Range(Cells(.Row 5, .Column), Cells(LastRow, .Column)) = quot;üquot;
End With
End Sub
--------------------With thanks to the dedication of some Excel gurus on another forum.
Cheers,
Mark.--
mevetts------------------------------------------------------------------------
mevetts's Profile: www.excelforum.com/member.php...oamp;userid=29130
View this thread: www.excelforum.com/showthread...hreadid=497252
- Jun 22 Fri 2007 20:38
Still haven't figured this one out
close
全站熱搜
留言列表
發表留言