how to pass on collected value to next step ?

R

RompStar

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
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.
 
Sort by date Sort by votes
hmmm..

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

like that ? I have it all on one line, and I get a syntax error:

hmmmm, not enough " or in wrong places ? hmmmm :unsure:
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sorry, now tested:

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

Note that it's rngFirstRow both times in the actual formula.
 
Upvote 0
Ok, I am posting a image of how the sheet looks, as you can see in column K, there are still a few dates below that are not counted because of how the data is entered, I can only sort by one row :- )

You'll see that 2/25/05 date ends in column K on row 82

but after a few date breaks it shows up again on row 85, this is because of how the date's are entered, in the human world the data entry is not perfect I am learning slowly that fact :- )

start -- HTML Maker paste here..
MASTER_selective_range_new.xls
IJKLMN
602/25/20053/2/2005Closedkm3
612/25/20053/2/2005Closedkm3
622/25/20053/2/2005Closedkm3
632/25/20052/25/2005Closedkm0
642/25/20052/25/2005Closedkm0
652/25/20052/25/2005Closedkm0
662/25/20052/25/2005Closedkm0
672/25/20052/25/2005Closedkm0
682/25/20052/25/2005Closedkm0
692/25/20052/25/2005Closedkm0
702/25/20052/25/2005Closedkm0
712/25/20052/25/2005Closedkm0
722/25/20052/25/2005Closedkm0
732/25/20052/25/2005Closedkm0
742/25/20052/25/2005Closedkm0
752/25/20052/25/2005Closedkm0
762/25/20052/25/2005Closedkm0
772/25/20052/25/2005Closedkm0
782/25/20052/25/2005Closedkm0
792/25/20052/25/2005Closedkm0
802/25/20052/25/2005Closedkm0
812/25/20052/25/2005Closedkm0
822/25/20052/25/2005Closedkm0
832/26/20053/2/2005Closedkm
842/27/20052/27/2005Closedkm
852/27/20052/25/2005Closedkm
862/28/20053/4/2005Closedkm
872/28/20053/4/2005Closedkm
882/28/20052/28/2005Closedkm
892/28/20052/28/2005Closedkm
902/28/20052/28/2005Closedkm
912/28/20052/28/2005Closedkm
922/28/20052/28/2005Closedkm
Master
 
Upvote 0
I'm not entirely sure what the problem is, but this code will put the formula in column N when the date in column J is greater than or equal to start date and the date in column K is less than or equal to end date:

Code: 
Sub Test()
    Dim rngFirst As Excel.Range
    Dim rngSecond As Excel.Range
    Dim Rng As Range
    Set rngFirst = Application.InputBox("Start", "Select Range", , , , , , 8)
    Set rngSecond = Application.InputBox("End", "Select Range", , , , , , 8)
    Set Rng = Range("J10:K" & Range("J65536").End(xlUp).Row)
    Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(rngFirst.Value)
    Rng.AutoFilter Field:=2, Criteria1:="<=" & CLng(rngSecond.Value)
    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
End Sub
 
Upvote 0
hmm, I think I am going to just do the search range on the column J only for the FROM and TO Range, ignore column K....

so I will change some things, and if I get stuck, I'll ask you :- )

Thank you for the help...
 
Upvote 0
ok, what about this:

in the first pop-up window when it ask for the first date range, I would
like for the user to be able to tyhpe in a date into the popup window

say 2/1/2005 START Range, to 3/1/2005

right now I click on the cell in column J to select the first range and then I scroll down and click on the end Range..

When I click on a cell it enters that range as say $J$25 - $J$30

when I type a date: say: 2/10/2005 I get an error box popup:

"The reference you typed in is not valid, or you did not provide a reference where one was required."

How can I enter the date value ?
 
Upvote 0
ok dude :- ) I changed it around a little bit because before for the input boxes it wouldn't accept a human date entry, which is what I want...

But I am confused as to how to pass these to the prefill of the network formula in column N.

Please keep in mind that I changed my mind about getting the ranges from two date columns, J and K, now I just want J, change of plans..

So Start Date from J and End Date from J, and of course, even if the dates are not sorted in column J, the network formula would only prefill the date ranges that I want... in column N..

hmmm :unsure: confused in Seattle, but learning a boat load :- )

start---

Sub FindByDateRange()

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

'Identifies range containing date. Currently set to column J

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

' 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
' Range("N" & BeginDate.Row & ":N" & EndDate.End(x1Up).Row).Formula = "=NETWORKDAYS(J" & BeginDate.Row & ",K" & EndDate.Row & ")-1"
' old: Set c = Range("N11:N" & LastRow)

Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(BeginDate)
Rng.AutoFilter Field:=2, 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
 
Upvote 0
Does it work if you change?

Code: 
Rng.AutoFilter Field:=2, Criteria1:="<=" & CLng(EndDate)

to:

Code: 
Rng.AutoFilter Field:=1, Criteria1:="<=" & CLng(EndDate)
 
Upvote 0
the last part of the script looks like this:

it doesn't prefill the network formula into N as of now, but the
parts above it work for collection of both date ranges...

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:
 
Upvote 0
Maybe the AutoFilter isn't working - it's temperamental with dates.

If you comment out these lines:

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

is the sheet filtered for the right start and end dates?
 
Upvote 0
You must log in or register to reply here.

Similar threads

MustacheKaboom
  • Solved
VBA Noob. All but the last step works... GAH!!
Replies
4
Views
346
MustacheKaboom
MustacheKaboom
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
246
B-radK
B
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
243
skillilea
S
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
431
AndyEd
A
D
  • Solved
Filter Last month bringing back unwanted records
Replies
7
Views
287
Damian37
D

Forum statistics

Threads
1,215,256
Messages
6,123,906
Members
449,132
Latest member
Rosie14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top