Variable Not Passed Back Correctly

Nov 11, 2006

All the subs below take place in Module4. I have a variable, MoreDates, defined as Boolean. At first it was just dimmed, now I have it as Public. When it's passed to a sub, it's true; in the called sub it's changed to false (and I see it as false when stepping through) and sub is exited. The next line of the calling routine checks if true/false...but it's true again. I'm not passing by value, so I don't know why this is happening.

MoreDates=True
Do Until MoreDates = False
Query_BigCharts (MoreDates) '***************
If MoreDates Then
temprow = temprow + 1
Range("A" & temprow).Select
lastdate = DateAdd("m", -1, lastdate) ' backs last-written date up a month
Range("D6").Value = lastdate 'send to eom cell
lastdate = Range("D8").Value ' formula in d8 converts to last weekday of the month.
End If
If lastdate < FromDate Then
MoreDates = False
End If
Loop
End Sub.............

View 6 Replies


ADVERTISEMENT

Match Date Passed To Variable

Dec 7, 2006

In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match

While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"

My code is as below

Private Sub cmdEffortDistribute_Click()
Dim lCount As Single
Dim lStartDate As Date
Dim lCounter As Single
Dim lBudget As Single
Dim lInitial As String
Dim lInitialdate As String
Dim lInitialWeek As String

I am also attaching excel sheet which I am using here for reference.

View 5 Replies View Related

Months Have Passed Irregardless The Number Of Days Passed

Mar 5, 2010

I need a formula to calculate how many months has passed irregardless how many days had passed. What I mean by that is if I have a starting date of 1/31/2010 and an end date of 2/1/28/2010, that should count as 1 month passed. I tried using the DATEDIF function, but that function is counting number of days, so if only 28 days had passed, that would not count as 1 month.

View 9 Replies View Related

Variable Not Assigning Correctly

Oct 31, 2006

The relevant code is below. I can post it all if necessary -- it's about 30 lines though. Can anyone recognize what the problem might be?

MsgBox "vSh " & vSh & " vOp " & vOp & "vCash " & vCash
vCash = vSh * vOp
MsgBox "vCash " & vCash

The MsgBox's are merely for debugging. When the code above runs, the first MsgBox I receive states similar to:
"vSh 14.238964 vOp 45.23 vCash 1000"
The second MsgBox, from after the multiplication, states:
"vCash 1000"

For some reason the vCash variable just will not set. At the beginning of the code, I declared each variable as Dim var, meaning as a variant, and whenever I debug by halting during a MsgBox display, each of the variables appear to be the same types: Variant/Double.

View 6 Replies View Related

Calculating Distances Between Back-to-back Locations

Aug 11, 2012

I have a layout something like the following:

A1
A2
A3

[Code]....

Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).

Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".

Ultimately I am looking to have a matrix which contains all the distances between each location:

A1
A2
A3
B1

[Code].....

View 4 Replies View Related

Time Passed Since Last Saved

Mar 8, 2007

Private Sub Workbook_Activate()
Dim user As String
If Worksheets("Setup"). Range("D6").Value = "" Then
user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name")
Worksheets("Setup").Range("D6").Value = user
MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.")
Else
user$ = Worksheets("Setup").Range("D6").Value
MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.")
End If
Exit Sub

but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.

View 5 Replies View Related

Highlight Row If Date In Column Is Passed

Aug 1, 2014

I want to highlight the rows in my worksheet when the dates in column 'N' are in the past. I've seen codes to do this using conditional formatting when searching online and in here but the problem is is that it highlights blank cells as well. Is it possible to correct this?

View 8 Replies View Related

Forecast Calculation - Once Month Has Passed

Jan 31, 2014

I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.

Attached is a sample spreadsheet with different vendors.

Rolling sum after month has passed.xlsx‎

View 5 Replies View Related

Trying Use Isblank & Vlookup When Date Has Passed

Jan 22, 2009

My original formula is:

=IF(ISBLANK(VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,FALSE)) = FALSE,VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,$E39),"")

I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)

I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)

This is so when players make a transfer it doesnt include the weeks before that date!!

View 12 Replies View Related

Inserting Data After A Date Has Passed

Jan 15, 2007

What formula would I use to populate a cell?

Example

e13 = 0 until after the 10th of the month on the 11th it = $100.00
If I13 has an amount in it on or before the 10th e13 remains $0.
Each of the 12 line of this rent roll needs to correspond to its own month.

View 10 Replies View Related

Automaticaly Set To Default After Date Passed

Mar 26, 2008

I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this

View 14 Replies View Related

Count The Number Of Events Passed

Apr 21, 2009

[data] ....

Here I have 9 total requirements to be tested in any event. If I wanted to count the number of events passed it wouldn't be 7. See Blah2 it passed Design and Electrical. Separate Events yes, but only one requirement. I need to have a total could of 1 for that as passed. Not two. I'm comfusing myself because this inherited spreadsheet is over 1,000 requirements.

View 9 Replies View Related

Formula To Get Number Of Passed Days

Feb 15, 2010

I have a formula which calculates number of days remaining. But I need a formula to to know how many days passed after a particular date.

Example :-

Date .Days passed
10-Feb-2010 .6

=IF(ISBLANK(G14),"",IF(TODAY() > G14,"LIC EXPIRED",G14-TODAY()))

View 9 Replies View Related

Length Of String Changes When Passed To Function

Sep 28, 2006

The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.

Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub

As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.

However, when I pass it to the following function in a module it outputs 2 as the length.

Function FormatTimeValue(vTarget As Integer) As String

Dim TimeStr As String

If IsNumeric(vTarget) Then

MsgBox Len(vTarget) '<= outputs 2

Select Case Len(vTarget)

Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"

View 3 Replies View Related

Highlighting Cells When A Period Of Time Has Passed?

Aug 20, 2014

If C2 = 21/06/14

I need F2 to highlight when 60 days have passed from the date entered in C2.

Would this work if placed in F2 =C2+60<TODAY()

No access to excel at the minute to try it

View 1 Replies View Related

IF Query; Find Out How Many People Passed In Green

Dec 22, 2008

I have a query where I am trying to find out how many people passed in green, amber and red from a series of data. I know (from the attached file) how to find out how many greens, ambers and reds there were, but how can I find out how many of the 7 in cell B12 passed in Green?

View 2 Replies View Related

How To Change Cell Colour After A Year Has Passed

Feb 9, 2009

I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?

View 12 Replies View Related

Looking For A Formula That Counts The Ones That Passed Or Failed The Audit?

Apr 25, 2013

I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.

View 3 Replies View Related

Lock Cells Passed Current Date

Apr 22, 2008

I want to make a sheet that will lock cells from rows that have passed the yesterday date. In A column i entred the days and the locked row must have the range from A to U. After i read the post from here Lock / Protect Rows Past Due Date any solution.

View 3 Replies View Related

Clear Formula If Date Has Passed Paste Contents

Jun 21, 2006

Have a macro where is some dates have passed the formulas are cleared but if the date is < than TODAY the value is left. All worked ok until I changed to formula to where the date calulated exclued weekends. Have attached example spreadsheet.
Forgot to add the marco was provided by the hard work of fellow Ozgrid memeber (Ger).

View 8 Replies View Related

List Random Numbers Being Passed To TextBox Controls

Sep 3, 2007

how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,

Sub timer()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
Dim x As String, i As Byte
Randomize
x = Format$(Int(Rnd * 1899) + 1, "0000")
For i = 1 To Len(x)
Me.Controls("TextBox" & i).Value = Mid$(x, i, 1)
Application.Wait Now + TimeValue("00:00:03")
Next

End Sub

Private Sub CommandButton1_Click()
timer
End Sub

Private Sub TextBox1_Change()

End Sub

View 3 Replies View Related

Shortening The Formula I Used For Calculating Number Of Public Holidays That Passed

Sep 25, 2009

I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.

=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5

From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula

*Note*
'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.

View 2 Replies View Related

Conditional Formatting :: Make Cell Blue After 90 Days Passed

Jan 19, 2010

This might be a simple answer but... if A1 had the date 1-Jan-2010, what formula would I used, within conditional formatting, to make that cell turn blue once 90 has passed? So the cell should turn blue on 1-Apr-2010.

View 10 Replies View Related

Lock Cells After Today's Date Passed (VBA Excel Code)

Apr 9, 2009

I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.

Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.

View 14 Replies View Related

Count Number Of Loops Passed Through And Display On Excel Status Bar

Jun 8, 2010

Is there a way to count the times a loop has occurred and be able to present a running total in the status bar of MS Excel?

Code:
Sub Master_Phone()
'
' Select cell A2, *first line of data*.

[Code].....

View 4 Replies View Related

Create Everyday Report From A Database For Workdays Of Current Month Passed

Jan 12, 2013

In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).

Every day, I update the numerical data of the database, with the facts of previous day.

In another sheet I've made some monthly, quarterly etc., reports for that data.

What I want, is to have:

1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.

2) the total workdays days for that period.

View 4 Replies View Related

VBA Code Line That Allows To Disregard Dates That Have Passed In Current Emailing Macro?

Apr 11, 2014

The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?

The code is as follows:

[Code].....

View 1 Replies View Related

Excel 2007 :: Auto Lock Rows 6 Days After Date Passed

Jul 30, 2014

I am new to Excel programming, haven't done any before and not familiar with creating/editing macros. Using Excel 2007.

I would like to automatically lock all the cells in a row if the date in column A in that row is >= 7 days old. Data is entered once a week (some time Sunday - Saturday) and I would like historical data (rows) to be locked so that they can't be edited inadvertently.

If possible could I also highlight the current working week somehow?

I have attached the worksheet : testworksheet.xlsx

View 3 Replies View Related

Excel 2007 :: Conditional Formatting Dates Based On Number Of Days That Have Passed?

Nov 22, 2011

I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.

For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.

Then do the same except change to Red after 60 days have passed the date.

I searched the forums and did not find this particular problem. I of course may have missed it too...

My guess was to try "$A$1

View 4 Replies View Related

Excel 2010 :: VBA Code That Handles Several Specific Parameters Or Wildcard Passed To Access

Jul 17, 2013

I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.

In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved