Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a date as a variable and run/convert formula against the vari
Good Day,
What I'm needing to do is retrieve a date field from a closed workbook, save it as a variable and then calculate a week number based on it. which I'll use for another function. I've attached two types of code I'm struggling with in VB and hope someone can help... The first part retrieves a date from a close workbook and I need to change "MsgBox" to a varible to save the date retrieved. Sub GetVal() MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ", "K6") End Sub Function TheValue(Path, WorkbookName, Sheet, Addr) As String Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets.Add Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!" & Addr TheValue = Range("A1").Value ActiveSheet.Delete Application.ScreenUpdating = True End Function This part is a formula that needs to read the date varible, "MYVAR", retrived from above and calculates a week number based on a fiscal year that begins 4/1...How would it be written in VB =ROUNDUP((("MYVAR"-MOD("MYVAR"-2,7)-DATE($A$2,4,1))/7)+1,0) Thank You in advance for taking time to look at this challenge. George |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert cell value from a formula to a valve on save | Excel Discussion (Misc queries) | |||
I NEED A FORMULA THAT TAKES THE DATE AND CONVERT IT IN A QUARTER | Excel Worksheet Functions | |||
What is the exact equation used in ToolPak t-test (unequal vari?) | Excel Worksheet Functions | |||
Convert formula to value based upon date | Excel Discussion (Misc queries) | |||
What is the formula to convert a whole column of date to text? | Excel Programming |