How do i change this code so that before going on to the next box, the entry must be: Monday, Tuesday...Sunday.
spellday = vbNullString
Do While Len(spellday) < 2
spellday = InputBox("Day of week")
If spellday = vbNullString Then
spellday = MsgBox("Press OK to Cancel Entry" & Chr(13) & "Press Cancel to return to program", vbOKCancel)
If spellday = vbOK Then
MsgBox "Program Closing" & Chr(13) & "Reopen file to run program."
ActiveWorkbook.Close
End If
End If
Loop
The code is ok until I hit a month with 4weeks in it and days left over. The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.
What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.
I hope i'm being clear
Sub NewSheets() Dim Dte As Date, Dy As Date Dim i As Long, j As Long, Dys As Long Dim CountWeek As Boolean Dim Shts As Long...
I am facing a problem while validating an Inputbox actually the issue is that when user press Cancel button on Input box it returns a empty string and also when user does not enters anything in Input box and pressess ok than too a Empty string is returned . Than how we will apply the following conditions :
1) If user does not enter anyting in InputBox and pressess OK than a msgBox should appear saying that "Uhav entered empty string and than looping back to inputbox"
2) If user pressess Cancel procedure should End
Sub try()
Dim Path Path = InputBox("Specify Path" , "Report Path")
If Path = "" Then
' Issue comes here becuse anyways code reahes here if user either lefts the Inputbox empty and presses ok OR user pressess cancel as i need to show different actions for 'different conditions
I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
Before my workbook is open the following code is run, which asks the user to enter the password. Depending on the password Range("name").Value takes values from the worksheet "Data" from table "M4:M20". The list of passwords is in the table "L4:L20". My code seems to be not optimal (too long). Does anybody know how to optimize this code?
Sub GoodMorning() On Error Goto Error 'Prompt the user for a password and unhide the worksheet if correct Select Case InputBox("Please enter the password", _ "Enter Password")
Case Is = "" Call HideSheets MsgBox "Sorry, that password is incorrect! Please contact the administrator.", _ ............
I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
I have the inputbox so i can set a string value, When the inputbox Cancle button is pressed i want to exit sub, If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname. If the inputbox has a value do >>>>>>That>>>>>
I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE.
Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09. Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09. Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
looking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?
fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.
I'm trying to do an IF function involving the date. Basically if the current day is a weekday then I want the cell value to be 30. If the current date is a weekend then I want the cell value to be 50. I'm pretty lost on how to write the formula.
Im working on a spreadsheet which has a column of dates formatted in "dd/mm/yy" there are always 365 days listed but these could be between any period. I am trying to create a table which looks up all mondays, tuesdays etc ....
I need a formula to find the first monday in the list and then to fill the rest of the tables. So far I manually input the monday and the rows below use =previous row + 7 to fill the following mondays, then in the next section it will refer to the monday and +1 to populate tuesday then the +7 in the rows below.
Ideally i would like a system which would log these dates automatically, the trouble is, sometimes the Thursday or another day will be first ( eg if the raw data is between 01/01/09 - 30/12/09 then the 01/01/09 is a thursday so the first thursday is 01 but the first monday will be 04/01/09.
I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.
I have a running schedule with mutlile tasks scheduled for different day # or the same and would like to have the dates auto fill in once I enter the start date.
example: 4/20/06 task 1 day 1 ? task 2 day 2 ? task 3 day 2 ? task 4 day 3
Keeping in mind that I don't want to include weekends and that I'll need to delete a range of holiday dates.
I have a sheet that contains a number of documents entered into a system by a user. Each month those number of documents are average by the number of working days in a month.
I'm using this formula, =AVERAGE(C4,22)
I would have to edit this for each month with the number of working days.
Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?
I have a list of dates in Column A, with a list of associated values in Column B. I'd like to create a small summary table that will give a count of the dates in Column A and a sum of the values in Column B, broken into a bucket for each day of the week.
I know how to create this table using a short macro that would loop through the list of dates, but if possible I'd like to calculate the values using worksheet formulas so that I don't have to run the macro each time another set of data is added to the list.
I am looking for excel to return a day from a date value, 14/07/07 = Saturday. Need this in VB ?
I have tried the DATE() and TODAY() etc.. Do i need to first tell excel a day by date so it can work it out, or can i do it in code???
The reason is im looking for it in VB to generate a report on a weeks data, which is inputted by a user. I will know the first date will be a range 1, but then need to convert it into a Day name ?
I have a cell that is set up as an external data source that is constantly changing 24 hours per day 5 days per week. What I am trying to do is copy that cell to another worksheet and save that dymanic data as a static value and save that data every 30 minutes synced to my PC clock.
I used a formula I found on this site to find the last friday in a month. = DATE(" & Str(iYear) & ",1+1,0)+MOD(-WEEKDAY(DATE(" & Str(iYear) & ",1+1,0),2)-2,-7)
Is there an equivelant date function that can be used when coding in vb (not within a cell.) The VBA editor does not recognize this. I believe it is because date is reserved for variables. If anyone can make this line of code work in vb so as I dont have to asign it to a cell in a worksheet you would be my hero.
I'm having problems on something that should be easy. On the basis of a date, just move to a pre-defined day-of-the-week cell. If the day is a Monday, the active cell will end up being the range set aside for Mondays.
Sub findFirstDay() Dim workDOW As Integer workDOW = Weekday(Range("b1")) Select Case workDOW Case workDOW = 1: Application.Goto reference:="sunFirstday" Case workDOW = 2: Application.Goto reference:="monFirstDay" Case workDOW = 3: Application.Goto reference:="tueFirstday" Case workDOW = 4: Application.Goto reference:="wedFirstDay" Case workDOW = 5: Application.Goto reference:="thuFirstDay" Case workDOW = 6: Application.Goto reference:="friFirstDay" Case workDOW = 7: Application.Goto reference:="satFirstDay" Case Else: Application.Goto reference:="wedFirstDay" End Select End Sub
The only case statement that executes is the "otherwise". I've tried working with WorkDOW as String, also to no avail.
Cell C1 contains the formula to return the day from the full date in B1... I tried the formula help....they return the number...but not the day... like monday or tue...how do i do it?
I have a column of dates: 1 day per row: 01/01/10, 02/01/10, 03/01/10, etc in sequence, without gaps (European date format). The list can begin with a different start date depending on initial user input and is not always a Monday. I want the user to be able to autofilter so that it only shows dates that fall on a Monday.
Private Sub Worksheet_Activate() Dim dDate As Date Dim strDate As String 'disable autofilter if already enabled With ActiveSheet .AutoFilterMode = False End With 'autofilter column a only, from a12, to filter Mondays If IsDate( Range("a12")) Then dDate = Range("a12") strDate = Weekday(dDate) Range("a12").AutoFilter Range("a12").AutoFilter field:=1, Criteria1:=strDate End If End Sub
