stop script if dates are missing, edit help...

[RompStar]

Below is my scipt: It asks for a two sets of date ranges, both from
column J, and then it does the rest.

Script works, no problem..

After the two sets of dates are entered for the range of column J, it
confirms the date Ranges, user clicks OK and then it goes on to the
next step.

I want it to change so that, If I enter let's say:

starting range: 1/1/2005

ending range: 3/1/2005 (from column J)

it would confirm the dates like it does now, but after the user pressed
ok, it would stop the script and pop-out a box saying:

Calculation can't be performed, missing Dates in selected Range..

Can Anyone help ?

Sub Main()

Dim LastRow As Long
Dim Rng As Range
Dim Msg As Integer
Dim BeginDate As Date
Dim EndDate As Date
Dim c As Range
Dim Item As Range
LastRow = Range("J11").End(xlDown).Row
Set Rng = Range("J11:J" & Range("J65536").End(xlUp).Row)


Do
Msg = vbOK
BeginDate = Application.InputBox("Enter Starting Date from column J:",
"Range Beginning", Type:=1)
If Not IsDate(BeginDate) Then

' Checks to see if entry is a date

Msg = MsgBox("Entry not a valid date!", vbCritical + vbRetryCancel,
"Error: Invalid Date")
End If
BeginDate = DateValue(BeginDate)

' Converts to date format

Loop While Msg = vbRetry
Do
Msg = vbOK
EndDate = Application.InputBox("Enter Ending Date from column J:",
"Range Ending", Type:=1)
If Not IsDate(EndDate) Then

' Checks to see if entry is a date

Msg = MsgBox("Entry not a valid date!", vbCritical + vbRetryCancel,
"Error: Invalid Date")
End If
EndDate = DateValue(EndDate)

' converts to date format

Loop While Msg = vbRetry
' -------------------------
MsgBox "You selected: " & BeginDate & " through " & EndDate & " ", ,
"Select Range"

' Do something with the date values

On Error GoTo Finish

' Fill in the selected cells in Column N with, skip dates Ranges not
selected...

Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(BeginDate),
Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 1)
Set Rng = Rng.SpecialCells(xlCellTypeVisible)
Rng.Offset(0, 4).FormulaR1C1 =
"=NETWORKDAYS(RC[-4],RC[-3],R2C3:R9C3)-1"
Rng.AutoFilter

' Make sure Holidays are discounted for that day, R2C3:R9C3
' Do Number coloring as a Visual Step...

Set c = Range("N11:N" & LastRow)
For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Set c = Range("N11:N" & Range("N65536").End(xlUp).Row)
For Each Item In c
If Val(Item) < -20000 Or Val(Item) > 20000 Then
Item.Value = "Missing Date!"
Else
End If
Next Item

Finish:
End Sub

script was pasted, and it works, if you see any wrapped lines, it's due
to formatting in the google post.. thanks

 

[RompStar]

I wrote this up to go after the:

On Error GoTo Finish

' If Dates are missing Print out a POP-UP Box to screen explaining
error

If DateValue(BeginDate) = "" Or DateValue(EndDate) = "" Then
MsgBox "Calculation can't be performed, missing Dates in selected Date
Range!"
GoTo Finish
Else
GoTo LastPart
End If

but not sure if this is good, what do you guys think ?

 

[Bob Phillips]

I would remove the Gotos, and exit

If DateValue(BeginDate) = "" Or DateValue(EndDate) = "" Then
MsgBox "Calculation can't be performed, missing Dates in selected Date
Range!"
Exit Sub
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Why did you drop the isdate() function?

What can you put into BeginDate that will make datevalue(begindate) return ""?

Instead of using an inputbox and having to worry about someone entering Jan 2,
2003 as 01/03/04 and someone else using 04/01/02 or any of the various
combinations, maybe you can use a calender control to let the user pick a date.

There's a link to a free calendar control at the bottom of Ron de Bruin's page:
http://www.rondebruin.nl/calendar.htm

Ron has some instructions on how to use it on that same page.

 

[RompStar]

well, the other way the way it seemed to worked was that the user had
to click on the start and end range with the mouse, and because there
is a lot of rows, like almost a 1,000, scrolling down to find it wasn't
what my boss wanted :- )

he wanted the ability to enter the start date in 1/1/2005 format and
then another box for the end date...

In the end it ended up working, but then I was told, I have to make
sure that if after the dates are entered, they are from the J column
for the start and end date, then if the dates in that range to the
right on column J, which is K, if any of those are missing to print out
a
msg box and basically stop and say: make sure the dates in the range
are non-blank..

So that's what I am trying to figure out, your example was great, but I
couldnt' enter the date, had to click on a range $A:$1, u know :- )

 

[RompStar]

ya I was thinking maybe to make 2 Subs() first one to look over that
range to make sure the dates for the range selected in J, that the
cells on the right side in that range in K are there, and if they are
then move over to the second sub which already works..

then I could play with that, calendar thing at home...

 

[RompStar]

also, someone gave me this idea:

Sub testrangeforblankdates()
Dim rg As Range
Dim NumBlanks As Integer
'----------------------------------
Set rg = ActiveSheet.Range("A1:E10")
NumBlanks = _
Application.WorksheetFunction.CountBlank(rg)
If NumBlanks > 0 Then
MsgBox (NumBlanks & " blanks.")
End If
End Sub


maybe I can figure out how to make that my first sub() and then move
over to the second if the dates are there, just have to figure out that
AutoFilter, I was reading more about it in a book I have here.. :- )

 

[Dave Peterson]

I'm guessing that you don't have any duplicate dates in column J (else how would
you know which of the duplicates is the start/end date?).

Maybe you could have a userform that shows the dates from your range. Once the
user selects the dates, you could validate (countblanks = 0) and proceed with
your real routine????

Debra Dalgleish has a get started page on userforms at:
http://www.contextures.com/xlUserForm01.html

I created a little userform with 2 commandbuttons (ok and cancel), 3 labels
(just some descriptions), and two comboboxes (to get the dates).

This is what the code looked like:

Option Explicit
Dim myStartRng As Range
Dim BlkProc As Boolean
Dim myTopCell As Range
Dim myBotCell As Range
Dim myDateRng As Range
Private Sub ComboBox1_Change()

Dim res As Variant
Dim myCell As Range
Dim myEndRng As Range

With Me.ComboBox2
If Me.ComboBox1.ListIndex < 0 Then
BlkProc = True
.Clear
BlkProc = False
.Enabled = False
Else
.Enabled = True
res = Application _
.Match(CLng(CDate(Me.ComboBox1.Value)), myStartRng, 0)
If IsError(res) Then
'shouldn't happen
MsgBox "design problem #1"
Else
Set myTopCell = myStartRng(res)
With myStartRng
Set myEndRng _
= .Resize(.Rows.Count - res, 1).Offset(res - 1, 0)
End With

For Each myCell In myEndRng.Cells
.AddItem Format(myCell.Value, "mmmm dd, yyyy")
Next myCell
End If
End If
End With
End Sub
Private Sub ComboBox2_Change()

Dim res As Variant

If BlkProc Then Exit Sub
If Me.ComboBox2.ListIndex > -1 Then
res = Application _
.Match(CLng(CDate(Me.ComboBox2.Value)), myStartRng, 0)
If IsError(res) Then
'this shouldn't happen either
MsgBox "Design error #2"
Else
Set myBotCell = myStartRng(res)
End If
If myTopCell Is Nothing _
Or myBotCell Is Nothing Then
'this shouldn't happen here
MsgBox "design error #3"
Else
Set myDateRng = Range(myTopCell, myBotCell)
If Application.CountBlank(myDateRng.Offset(0, 1)) > 0 Then
Me.Label3.Caption = "Blanks in adjacent column!"
MsgBox "Blanks in adjacent column"
Me.CommandButton1.Enabled = False
Else
Me.CommandButton1.Enabled = True
End If
End If
End If
End Sub

Private Sub CommandButton1_Click()
MsgBox "this is where you'd do all the work!"
Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
Set myStartRng = .Range("j3", .Cells(.Rows.Count, "J").End(xlUp))
End With

With Me.ComboBox1
For Each myCell In myStartRng.Cells
.AddItem Format(myCell.Value, "mmmm dd, yyyy")
Next myCell
End With

Me.Label1.Caption = "Begin Date"
Me.Label2.Caption = "End Date"
Me.Label3.Caption = ""

Me.ComboBox2.Enabled = False
With Me.CommandButton1
.Enabled = False
.Caption = "Ok"
End With
Me.CommandButton2.Caption = "Cancel"

End Sub

I did use the long format of the dates. I find that these lead to less
confusion.

 

[RompStar]

ya, that sounds good :- ) let me play with it...

Now when I look at the autofilter R1C1 formula, things make a lot more
sense :- )

Thanks for taking the time :- )

 

[RompStar]

looking back at the old code, I am trying to understand something, it
be a while before I can learn everything to understand things :- )

Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 1)

from reading the book, I know that, (1, 0) it's row and column offset,
so offset the row by 1 and column by 0...

Rigth now my header field is at row 9, so if I want to count the row in
10, I would want that to be (0, 0) right ?

But what does the:

Resize(Rng.Rows.Count - 1, 1) do ? the -1, 1 ?

Because when I run the dates, even if the date in row 10 is 1/1/2002
and if I don't ask for the range to be to include that date, it still
counts
that first row 10, after that, starting with 11, it's all good, so I am
thinking it has to do with the:

Resize(Rng.Rows.Count - 1, 1) do ? the -1, 1 ?

 

[Dave Peterson]

When you apply data|filter|autofilter, the autofilter range includes both the
header row and the data rows.

So

rng.offset(1,0)
drops down one row--but keeps the same number of rows.

So if your autofilter range was A1:Z100, then
rng.offset(1,0)

would be A2:Z101 (offset by one row).

The .resize() changes the number of rows or columns in that range.

so .resize(rng.rows.count-1,1)
would be to make it one row smaller than the original, and only one column wide.

..resize(100-1,1)
or
..resize(99,1)

So if rng were A1:Z100
rng.offset(1,0).resize(rng.rows.count-1,1)
would represent:
A2:Z100
(Just the data portion in the first column)

===
One thing you may want to consider (and it usually doesn't matter...)

rng.resize(rng.rows.count-1,1).offset(1,0)
will represent the same range.

But if rng were the whole column, (A1:Z65536), then doing the .offset(1,0) first
will blow up real good. (You're trying to get to A2:Z65537 and there ain't that
many rows in a worksheet.)

Everyonce in a while, it matters. If I'm thinking, I'll try to do the .resize()
first.

 

[RompStar]

So, if Range was A1:A100 and
rng.offset(1,0).resize(rng.rows.count-1,1)

that would offset the first rows by one A2:101, the A1 field would be
skipped if it had header info, right ?

and the -1,1 would drop it by one row A2:A100 ? and 1 column wide ?, so
if I put in -1,0 that would make it
0 column wide ? how would that affect that ? and 2, 2 columns wide,
this is where I am a little confused.

I'll get to the other code later that you spent your time on, I want to
make sure I understand everything in
the previous code first :- )

thanks again for your time...



would make it one row smaller ? so skip the first row, what does the
resize do exactly ?

 

[Dave Peterson]

The .offset(1,0) is what drops it down one row.

The .resize(rng.rows.count-1,1) is what changes the size of the range--from 100
rows by (whatever columns) to 99 rows by 1 column.

If you wanted to drop it down 7 rows and make it 23 rows smaller and 12 columns
wide (pretty weird--but just for talking purposes):

set rng = rng.resize(rng.rows.count-23,12).offset(7,0)

Does that help?

===

And if you try to make a range 0 columns wide, you're gonna have some trouble
with VBA. It won't like that at all.

 

[RompStar]

yes, it does, now I fully understand it, I feel good, bugs me if I
don't :- ) ok thanks!!!!!!!!!!!!