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