edit network script for window pop-up, store values... help..

[RompStar]

ok, I have a script that compares two dates:

A scheduled pickup date - column J

and

The Acctual Picked up date - column K

in order to be compliant, the time frame between these two dates
can't not take longer then 2 days from the scheduled pickup date.

Right now, the script when I run it, looks at all the data and in
column N
prefilles the =NETWORKDAYS formula and then runs the statistics,
showing
anything less then 0 as red and more then 2 as red, 0, 1, 2 as black.

Then on the spreadsheet I have a formula that looks at the column N
and counts the number ranges and figure out the percentage.

So basically, J and K have the dates, and N prints the end results
based on the date data, there are other columns, but they are
irrelavant to this script.

What I want to change, is when I press the play script button, it pops
up 2 screens at the user. In the first one it would ask the the date
range from the column J (scheduled pickup date), user enters that,
presses enter, and then another box pops up asking for the Pickup Date
Range from the K column.

And then it would run the number comparisons only on those date ranges,
so for example, if I type:

4/1/2005 for J, and 4/6/2005 for K, and press enter, it runs the rest
of the script and enters the numbers in the Columns N for rows that
match that selected date range: Here is my script so far...

start ---

Sub SelectiveFormatandfillformula()

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

On Error GoTo Finish

Range("N11:N" & Range("a65536").End(xlUp).Row).Formula = _
"=NETWORKDAYS(J11,K11)-1"

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 ---

Help me out guys, still a newbie :- )

I guess I will need two new variable, c1 and c2, for the two pop-up
windows, unless I can do it all in one pop-up window :- )

set c1 = Application.InputBox("Enter for J", how to store the value ?

set c2 = Application.InputBox("Enter for J", how to store the value ?

thanks.

 

[Jim Cone]

This ought to get you started...
'---------------------------------------
'Jim Cone
'San Francisco, USA
'April 11, 2005

Sub FindWhatWeToldThem()
Dim rngFirst As Excel.Range
Dim rngSecond As Excel.Range

StartOverOne:
On Error Resume Next
Set rngFirst = Application.InputBox("Select cell with date from column J", _
" Step One from Romp Star", , , , , , 8)
On Error GoTo 0
If TypeName(rngFirst) <> "Range" Then
Exit Sub
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"
GoTo StartOverOne
End If
End If

StartOverTwo:
On Error Resume Next
Set rngSecond = Application.InputBox("Select cell with date from column K", _
" Step Two from Romp Star", , , , , , 8)
On Error GoTo 0
If TypeName(rngSecond) <> "Range" Then
Exit Sub
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 & " and " & _
rngSecond.Value & " ", , " Romp Star Did It"
'Do something with the date values

Set rngFirst = Nothing
Set rngSecond = Nothing
End Sub
'---------------------------------

 

[RompStar]

thanks dude, people like you go straight to heaven :- )

I am learning a lot...

 

[RompStar]

I will closely look at this code and study it, that along with other
code, in the past
week along I have learned a lot :- )

 

[Jim Cone]

You are welcome.
Jim Cone

 

[RompStar]

Jim,

Ok, finally found time to try your help, trying to combine that with
the rest of the script, basically after I select the start range from
column J, and end Range from K, next I want to pass these Range values
that I just collected to the next process:

Column N is where the results will be printed to by the script, so
first I have to fill the selected range with this Formula:
=NETWORKDAYS(J11,K11)-1

Then after that formula is filled in, the next step is to highlight the
numbers in Column N,
that the formula has filled in and highlight < 0 Red, > 2 Red, leave 0,
1, 2 black..

By I think I am not passing on the values right or selecting my ranges,
still learning :- )

Can you help with that last part ?

here is my script... as it stands now...

start ---

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

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 ending Date Range from Column K

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...

On Error GoTo Finish

' Fill in the selected cells in Column N with Formula

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

' old range
' Range("n11:n" & Range("a65536").End(xlUp).Row).Formula =
"=NETWORKDAYS(J11,K11)-1"

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 here

The script work for the first two pop-up windows, I have to click with
the mouse on the cell and the values are filled into the popup window,
entering manual date, gives me errors, but the click I guess is even
easier...

After that, I will to fill the formula, and run the highlights, which I
know work already.

Thank you for your time....

Ignore and text wrapping in the script paste, it's how google formats
my posts, I am
aware of that :- )

 

[RompStar]

what about this, is but I get a wrong syntax :- )

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


am I close ? all on one line in my vb script..