how to pass on collected value to next step ?

[RompStar]

Ok, I have a script that pops up two windows and ask for two date values, one from the J column and one from the K column...

Then it confirms what the user entered and goes on to the next step, I am having trouble passing on the collected values to the next step, as I am still learning the syntax.

the two values that I collect are, here is the script as it stands now....

start script -----

Sub FindByDateRange()

' User will be asked to enter Start Date from J and End Date from K to
' figure out comppliance on a selected date range from pop-up windows.

Dim rngFirst As Excel.Range
Dim rngSecond As Excel.Range
Dim c As Range
Dim LastRow As Long
LastRow = Range("N11").End(xlDown).Row

' Ask for the starting Date Range from Column J, Scheduled Pickups

StartOverOne:
On Error Resume Next
Set rngFirst = Application.InputBox("Enter Starting Range from _
Scheduled Pickups, Column J", "Select Range", , , , , , 8)
On Error GoTo 0
If TypeName(rngFirst) <> "Range" Then
Exit Sub

' 10 represents the, 10th column or column J

ElseIf rngFirst.Column <> 10 Then
MsgBox "Wrong column"
GoTo StartOverOne
Else
Set rngFirst = rngFirst(1)
If Not IsDate(rngFirst.Value) Then
MsgBox "Select a date format"
GoTo StartOverOne
End If
End If

' Aks for the ending Date Range from Column K,
' Acctual Pickup Date ( -2 for compliance )

StartOverTwo:
On Error Resume Next
Set rngSecond = Application.InputBox("Enter Ending Date Range _
from Pickup, Column K", "Select Range", , , , , , 8)
On Error GoTo 0
If TypeName(rngSecond) <> "Range" Then
Exit Sub

' 11 represents the, 11th column or column K

ElseIf rngSecond.Column <> 11 Then
MsgBox "Wrong column"
GoTo StartOverTwo
Else
Set rngSecond = rngSecond(1)
If Not IsDate(rngSecond.Value) Then
MsgBox "Select a date"
GoTo StartOverTwo
End If
End If

MsgBox "You selected: " & rngFirst.Value & " through " _
& rngSecond.Value & " ", , "Select Range"

' Do something with the date values

' Pass on values from rngFirst and rngSecond to the rest of the script...

' ------------------------ LAST PHAZE --------------------------
' Pass on collected values...

On Error GoTo Finish

' Step #1
' Fill the selected range of cells in column N with this fomula
' then go to the final STEP #2

Range("n11:rngFirst", rngSecond.End(xlUp).Row).Formula = "=NETWORKDAYS(J11,K11)-1"

' Step #2
' After selected cell ranges are fill with NETWORKDAYS formula
' run through them again,


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

Finish:
End Sub

end script -----

Anyways, I am having trouble passing on the two variables values correctly to the STEP #1 after ---- LAST PHAZE ----


Basically, columns J and K have date values, they are compared with the
formula in column N and then the results are highlighted with red is < 0, > 2, and anything that is 0, 1 and 2 stays black.

thanks guys.. your help is appreciated.

 

[RompStar]

ok, thanks...

 

[RompStar]

see, also that AutoFilter I don't think is working right:

here is a cut of my sheet, I selected 12/28/2004 - 12/29/2004

and it's selecting ranges in column N that are not within this
range, what do you think could be wrong, something with the ?

Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(BeginDate)
Rng.AutoFilter Field:=1, Criteria1:="<=" & 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])-1"
Rng.AutoFilter

??

MASTER_5_final.xls
	J	K	L	M	N
11	12/31/2004	1/10/2005	Closed	dk
12	12/28/2004	1/4/2005	Closed	dk	5
13	12/29/2004	1/4/2005	Closed	dk	4
14	12/28/2004	1/4/2005	Closed	dk	5
15	12/29/2004	12/29/2004	Closed	km	0
16	12/29/2004	12/31/2005	Closed	dk	262
17	12/30/2004	1/12/2005	Closed	dk
18	12/28/2004	1/4/2005	Closed	dk	5
19	12/29/2004	1/4/2005	Closed	dk	4
20	12/28/2004	12/30/2004	Closed	dk	2
21	12/29/2004	12/31/2004	Closed	dk	2
22	12/30/2004	1/4/2005	Closed	dk
23	12/30/2004	1/5/2004	Closed	dk
24	1/4/2004	1/5/2005	Closed	dk	262
25	1/4/2004	1/5/2005	Closed	dk	262
26	12/30/2004	1/4/2005	Closed	dk
27	1/6/2004	1/7/2005	Closed	dk	263
28	12/30/2004	12/30/2004	Closed	dk
29	12/31/2004	1/6/2005	Closed	dk
30	12/31/2004	1/6/2005	Closed	dk
31	1/4/2004	1/13/2005	Closed	dk	268
32	12/31/2004	1/7/2005	Closed	dk
33	12/31/2004	1/11/2005	Closed	dk
34	12/30/2004	1/4/2005	Closed	dk
35	1/4/2004	1/4/2005	Closed	dk	261
36	12/30/2004	1/4/2005	Closed	dk
37	1/6/2004	1/4/2005	Closed	dk	260
Master

 

[Andrew Poulsom]

Oh sorry, I forgot to alter the code, which was filtering columns J and K, to just filter on column K with an AND.

So replace:

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

with:

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

 

[RompStar]

This is for K ? I get my dates from J, so I would need it for J the autofiltering ?

 

[RompStar]

when u look at Row11, COlumn J you see 12/31/2004

I tried to make a search for dates

12/31/2004 - 12/31/2004 just for that 1 day..

and finds them correctly, but skips the first row that also has the date 12/31/2004 in J, what would I need to adjust so that it reads row 11 too ?

For now I simply added another row above 11, so that first row that's skipped is empty anyways...

Also I would like to understand this, because my ultimate goal is to learn this through-out.

If you have time and don't mind, can you explain what does what in ?

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])-1"
Rng.AutoFilter

thank you.

 

[RompStar]

also another problem, which isn't a large problem right now, but I want my statistics to be right.. as you see I use that NETWORKDAYS formula, which skips weekends Sat and Sun to do it's calculations based on the working days Mon - Fri.

There are also some holidays, in 2005, we have here 6 more holidays left.

like for example in May coming up on May 30 is memorial day and that's a monday, I would want that skipped.

Any good way to tell networkdays to look at a list of holidays, maybe I can put them in the sheet or in the VB it's self and for any day that's a holiday it would discount that as -1 from the compliance, because no one is at work, so we can't count it.

 

[RompStar]

ok I listed my holidays: 8 of them for 2005, in cells c2:c9

one date on each cell of column C

normally it would be

=NETWORKDAYS(J12,K12)-1

now it's

=NETWORKSDAYS(J12,K12,C2:C9)-1

is this right ?

also how would that be written using your format ?

Rng.Offset(0, 4).FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])-1"

 

[Andrew Poulsom]

Record a macro while entering the formula and you will get the code.

For your missing date, I assumed a header was in row 11. If it's in row 10 change the starting row accordingly.

 

[RompStar]

thanks, I'll look into it..

 

[RompStar]

do you think I did this right ?

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
If Val(Item) < -20000 Then
Item.Value = "N/A"
End If
Next Item

I want also any negative values that come up as part of the formular calculations to be replaced with the word N/A, it compiles without errors, but doesn't seem to work, did I do it wrong ?