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]

for filtered you mean, like autofilter sort ? i commented it out, but I have no way of seeing sheet filtering, maybe I am confused here..

what am I supposed to be looking for ?

the dates in column J right now are not in a perfect sort order and in the real world that's how it it.. so I want rows to be skipped if they don't fall within the

START Date -- END Date Range..

 

[RompStar]

here is my whole script as it reads now, maybe I made some mistakes, please take a look...

start ----

Sub Main()

Dim rngDate As Range
Dim Msg As Integer
Dim Dates As Long

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


Set Rng = Range("J11" & Range("J65536").End(xlUp).Row)
Set rngDate = Range(Range("J11"), Range("J65536").End(xlUp))

Do
Msg = vbOK
BeginDate = Application.InputBox("Enter the beginning date _
from column J:", "Range Beginning")
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 the end date _
from column J:", "Range Ending")
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

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

Set c = Range("N11" & 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

 

[Andrew Poulsom]

This worked for me:

Sub Main()
    Dim LastRow As Long
    Dim Rng As Range
    Dim Msg As Integer
    Dim BeginDate
    Dim EndDate
    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 the beginning date from column J:", "Range Beginning")
        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 the end date from column J:", "Range Ending")
        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
    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
    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

The AutoFilter wasn't working because:

Set Rng = Range("J11" & Range("J65536").End(xlUp).Row)

just concatenates J11 with the number of the last row. "J11" needs to be "J11:J". Similarly in:

Set c = Range("N11" & LastRow)

"N11" needs to be "N11:N"

 

[RompStar]

honestly thank you for your help, I am learning a lot, many pieces now I can use to do other things with and I am reading books of course at the same time..

Thank you again!!!

 

[RompStar]

I would use this to make sure that date data is pickup from column J ?

ElseIf rngFirst.Column <> 10 Then
MsgBox "Wrong column"
GoTo StartOverOne
Else

can you help me to insert that correctly ? I am going to try my self right now.

 

[Andrew Poulsom]

I don't see rngFirst set anywhere. I thought you had resorted to just having the user type in a date.

 

[RompStar]

here is how the script looks, I want to make sure that both date values, Start and End are collected for the dates from column J only...

---- start

Sub Main()



Dim LastRow As Long
Dim Rng As Range
Dim Msg As Integer
Dim BeginDate
Dim EndDate
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")
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")
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)
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

' 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
Finish:
End Sub

 

[Andrew Poulsom]

So why don't you revert to your original method of having the user point at a cell?

 

[RompStar]

Because the user that I am trying to build this for, he wants the ability to enter dates... as the data for the column J that has dates has lots of data like 2000 rows and it would be easier to enter a date... start and end...

 

[Andrew Poulsom]

Check out the Find method in VBE Help. And read this about finding dates:

http://www.cpearson.com/excel/DateTimeVBA.htm#Finding