PDA

View Full Version : Change days each time



headworth
07-11-2010, 01:11 AM
I am trying to enter days of the week each time i press button new day this code has error
user defined type not defined can you help?

Private Sub CommandButton1_Click()

Dim i As Long
Dim Count As Long

Count = 1
For i = 2 To 65536

If Cells(i, 1) <> "" Then
Count = Count + 1
End If
Next i

Dim day As day
day = Cells(Count, 1).Value
day = day + 1


'Days of the week Monday to Friday
Sheets("Sheet5").Cells(1, 1)(Rows.Count, "D").End(xlUp).Offset(1).Value = day

End Sub

mdmackillop
07-11-2010, 01:43 AM
Hi Headworth
Always use Option Explicit. That will show a basic error.
While a good start, the code is a bit inefficient. Can you post a sample workbook so I can see your data types, layout and desired result?
Use Manage Attachments in the Go Advanced reply section.

Bob Phillips
07-11-2010, 02:06 AM
There is no such datatype as Day. To count days, which is just a number, use a type Long.

But I am totally confused what you are doing, what is your requirement?

YOu can count all of the non-empty cells with a simple


Count = Application.CountA(Columns(1))


no need for a loop.

headworth
07-11-2010, 04:12 AM
I have changed the code it how works but i still need some help what i am trying to do is when i press the button each time i need it to change the day like Mon, Tue, Wed would it be a If statement to say the cell befor this has Tue then this cell wed?
Private Sub CommandButton1_Click()
Dim i As Long
Dim Count As Long

Count = 1
For i = 2 To 65536
If Cells(i, 1) "" Then
Count = Count + 1
End If
Next i

Dim day As String
Dim mon As Long
day = Cells(Count, 1).Value
day = mon + 1

'Days of the week Monday to Friday
Sheets("Sheet5").Cells(1, 1)(Rows.Count, "D").End(xlUp).Offset(1).Value = "Mon"
End Sub

Bob Phillips
07-11-2010, 04:37 AM
If you won't explain what you are trying to do, it is difficult to help.

headworth
07-11-2010, 04:48 AM
If column D row 1 has the word Mon then if i press CommandButton it should put Tue in column D row 2

Bob Phillips
07-11-2010, 08:32 AM
Just once, or should the next one, row 3, be Wed? And where do you stop?

headworth
07-11-2010, 09:11 AM
Yes next one wed and stop at Fri

Bob Phillips
07-11-2010, 02:57 PM
Private Sub CommandButton1_Click()
Dim aryDays As Variant
Dim DayIdx As Long

aryDays = Array("Mon", "Tue", "Wed", "Thu", "Fri")

With Sheets("Sheet5")

If .Range("D1").Value2 <> "" Then

On Error Resume Next
DayIdx = Application.Match(.Range("D1").Value2, aryDays, 0)
On Error GoTo 0
If DayIdx > 0 Then

.Range("D1").AutoFill .Range("D1").Resize(6 - DayIdx)
End If
End If
End With
End Sub

headworth
07-12-2010, 03:26 AM
Thank you for your help if only i could put into words what help i am asking for
your code works great.

As I did not ask the right question this code will not help me but i will work on your code to try to make it work for me for your code changes the days. The code I am work on has to be for entry into a database weekly and the days have to be for 15 entrys for each day Monday to Friday

Regards
Headworth

mdmackillop
07-12-2010, 10:06 AM
Refer to Post 2

headworth
07-12-2010, 11:58 AM
The go advanced will not let me select Attachment

headworth
07-12-2010, 05:24 PM
Have sent you sample of data

rbrhodes
07-12-2010, 06:24 PM
XXXXX

http://www.ozgrid.com/forum/showthread.php?t=144255

rbrhodes
07-12-2010, 07:19 PM
Hi HW,

Xpost dont'cha know.

Slightly different approach here. All info is on sheet 'Data', layed out as Mon to Fri with shift info, etc. Macro simply copies all from 'Data' to sheet 'Shifts' on next available row.

This way anything you change, update or delete on sheet 'Data' will be copied to sheet 'Shifts' on next button click - no need to change all that code.

headworth
07-12-2010, 07:57 PM
Hi rbrhodes

Thank you this works very nicely

Regards
Headworth

rbrhodes
07-12-2010, 07:59 PM
You're welcome! but do me a favour and read about Xposting,