Change days each time

[headworth]

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?
[vba]
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
[/vba]

[mdmackillop]

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]

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

[vba]
Count = Application.CountA(Columns(1))
[/vba]

no need for a loop.

[headworth]

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?
[vba] 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 [/vba]

[Bob Phillips]

If you won't explain what you are trying to do, it is difficult to help.

[headworth]

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]

Just once, or should the next one, row 3, be Wed? And where do you stop?

[headworth]

Yes next one wed and stop at Fri

[Bob Phillips]

[vba]

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
[/vba]

[headworth]

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]

Refer to Post 2

[headworth]

The go advanced will not let me select Attachment

[headworth]

Have sent you sample of data

[rbrhodes]

XXXXX

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

[rbrhodes]

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]

Hi rbrhodes

Thank you this works very nicely

Regards
Headworth

[rbrhodes]

You're welcome! but do me a favour and read about Xposting,