Solved: Check date & compare to another date

[simora]

I have a User form and I want to post data to the next available row on worksheet 1
IF .Row > 2 Or .Row < 50

But I also need to check the Date in Col A of the row before it.
If The date in Col A on the previous row is different, I need to have a blank row between the data, so I'll be posting to 1 Row further down.

How do I check to see if I am on row 2 and NOT create a blank line, but IF NOT check the previous date in Col A before I post the data.

The form is posting the date to col A from TxtBox 1 and other data to Cols B, C, & E

Any code and suggestions welcome.

Thanks

[Bob Phillips]

Off the top


If Range("A1").Value = "" Then

NextRow = 1
ElseIf Range("A2").Value = "" Then

NextRow = 2
Else

NextRow = Range("A1").End(xlDown).Row+1
End If

If NextRow > 2 And NextRow < 50 Then

If myDate = Cells(NextRow - 1, "A").Value Then

NextRow =NextRow +1
End If
End If

Cells(NextRow, "A").Value = myDate
'etc.

[simora]

xld:
Thanks;
What I was really looking for is a way to compare the previous date to the new date entered on the UserForm. I've got most of that done, except ;

How do I use an onChange event in a UserForm text box to format the date exactly as it is on the worksheet.
If I dont do this, the date value keeps insisting that the dates are not equal. Dates are entered on TextBox5

Code Sample:



Dim DateOl
Dim NewDate
DateOl = Range("A65536").End(xlUp).Offset(1, 0).Value
NewDate = Me.TextBox5.Value
If DateOl = NewDate Then

Set c = Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
c.Value = Me.TextBox1.Value
c.Offset(0, 4).Value = Me.TextBox5.Value
Else
Set c = Range("A65536").End(xlUp).End(xlUp).Offset(2, 0)
c.Value = Me.TextBox1.Value
c.Offset(0, 4).Value = Me.TextBox5.Value

[mdmackillop]

By definition, Range("A65536").End(xlUp).Offset(1, 0).Value
will always be empty, so it will never equal a date value

[simora]

mdmackillop:

Thanks.
I worked around that problem.

[mdmackillop]

If you have a solution, please post it for the benefit of others.

BTW, Please use the VBA button to format code as shown.

[simora]

I changed the way I was going to use the date compare feature. The problem was that the Old Date was formatted as a date entry from a UserForm, but the new date was today's date formatted using Now()

Trying to get 1 date = to the other would never have worked because Excel kept comparing the Date string to Now() and concludes that they'll never be equal.

I simply just start each new day 1 line down with Today's date printed out in RED BOLD letters Directly on the worksheet.

If anyone could figure out how to get
Thursday, October 01, 2009 = NOW() I'd be interested in finding out.

Cheers

[Bob Phillips]

Don't use NOW(), use TODAY()

[simora]

Thanks :

I had tried the Today function also. That didn't work out either. I think the problem lies is the way the 2 dates were compared. The old date on the worksheet was formatted on the worksheet, and the current date was still on a userform as either NOW() or Date() or something like that. A lot of effort, so I just finally gave up and did the work around like I posted.

[mdmackillop]

Try this

Option Explicit

Private Sub UserForm_Initialize()
TextBox1 = Date
End Sub

Private Sub CommandButton1_Click()
Dim Tgt As Range
Set Tgt = Cells(Rows.Count, 1).End(xlUp).Offset(1)
If Tgt.Offset(-1) <> CDate(TextBox1) Then Set Tgt = Tgt.Offset(1)
Tgt = CDate(TextBox1)
Tgt.NumberFormat = "dddd, mmmm dd, yyyy"
Tgt.Offset(, 1) = TextBox2
Tgt.Offset(, 2) = TextBox3
Tgt.Offset(, 3) = TextBox4
End Sub

[simora]

Hi mdmackillop:

Thanks.

I included your code. Works great. HOWEVER,
Now that I've reworked everything, I have a seperate box on the form where users can enter TODAY's date, because some of the entries are for other dates. I'm trying to go to the worksheet and do a SUMIF on 2 seperate columns based on that date and show it as labels on the User form.

How do I get my SUMIF to work in this fashion? The date value is in TxtBox 11 and that date also gets posted to U2

The code I tried was along these lines



Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11.Value <> "" Then
Frame4.Visible = True
Me.Label17.Caption = ThisWorkbook.Sheets(1).Range("R2")
Me.Label21.Caption = ThisWorkbook.Sheets(1).Range("S2")
End If



My worksheet formulas are like this
=SUMIF(A2:A50,U2,C2:C50) or
=SUMIF(A2:A50,U2,D2:D50)

[simora]

Hi All:

Got my labels to show correctly, however, now my userform only posts after I close it. Any ideas?

This was the code to display the labels correctly.



Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11.Value <> "" Then
Sheets(1).Range("U2").Value = TextBox11.Value
Frame4.Visible = True
Me.Label17.Caption = ThisWorkbook.Sheets(1).Range("R2")
Me.Label21.Caption = ThisWorkbook.Sheets(1).Range("S2")
End If
End Sub

[simora]

vbModeless. problem.

Re-setting to vbModeless. on the User form solved it.

Thanks to all for your help.