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.

 

[Andrew Poulsom]

Can you give an examples of the addresses of rngFirst and rngSecond and specify what the Range address should be in?

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

 

[RompStar]

Ok, rows 0 to 10, have headers and formulas, the data always starts at row 11, I forgot to mention, so 10 and down, columns J and K have date values, and they are long, for many months, so when I play this script it asks for a starting date range in the J column and K that I want the statistics run for...

column J is the receive date, and K completed date, 2 days for complinace, little project that I am working on...

so if the data runs from say 1/1/2005 through 4/1/2005 and I select

2/1/-5 as the starting range and 3/1/05 ending range, it would only fill in the networkdays formula in that range in column N and leave other cells in that column empty.

 

[Andrew Poulsom]

So please can you give an examples of the addresses of rngFirst and rngSecond and given those specify what the Range address should be in?

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

 

[RompStar]

I play the script and for the first rngFirst, for this example, let's say:

$J$24 for the starting range column J

$K$82 for the ending from column K

and from this range, in column N it woulod pre-fill that
networkdays formula and then run the numbers for the last part...

Is this what u mean ?

 

[Andrew Poulsom]

Now we have J24 and K82, what range should be filled with the formula? And what should the formula be for the first cell in that range?

 

[RompStar]

ya so,

at row 24 in column N, it would start to fill the formula

=NETWORKDAYS(J11,K11)-1

through row 82 but in column N

and then when the last part #2 runs, it would only run the cells
in column N that have the formula in them, the rest would be blank.

 

[Andrew Poulsom]

So the Range should be?

MsgBox Range("N" & rngFirst.Row & ":N" & rngSecond.Row).Address

Are you sure you want the formula?

NETWORKDAYS(J11,K11)-1

in cell N24? Should it be the same formula in all the cells?

 

[RompStar]

well the formula would be:

NETWORKDAYS(J##,K##)-1

where J## is the starting range, so if the starting range is on row

24 it would be

NETWORKDAYS(J24,K24)-1

and for each row it would automatically increment, 25, until 82

 

[Andrew Poulsom]

OK, try:

Range("N" & rngFirst.Row & ":N" & rngSecond.Row).Formula = "=NETWORKDAYS(J" & rngFirst.Row & ",K" & rngFirst.Row)-1"