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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a date as a variable and run/convert formula against the vari
Hi George,
Don't know where you are picking up the YEAR from so I made a variable called iYEar just for this example. I'm not exactly sure what you're looking for but I hope this helps. I think you might have been having trouble with how to translate the MOD( ) function into VBA. I created a separate variable (iMod) for that. '------------------------------------------------- Sub GetVal() Dim strPath As String Dim strWorkbook As String Dim strWorksheet As String Dim strAddress As String Dim dtDate As Date Dim iYear As Integer Dim iMod As Integer strPath = "C:\GssReports" strWorkbook = "gssreport MTTR.xlsx" strWorksheet = "gssreport 1 " strAddress = "K6" iYear = 2010 dtDate = _ TheValue(strPath, strWorkbook, strWorksheet, strAddress) iMod = dtDate - (7 * Int(dtDate / 7)) MsgBox Application.WorksheetFunction.RoundUp(((dtDate - _ iMod - DateSerial(iYear, 4, 1)) / 7) + 1, 0) End Sub '------------------------------------------------- -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "George" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a date as a variable and run/convert formula against the vari
Option Explicit
Sub GetVal() Dim myVar As Date 'or variant if you're not sure if it's always a date Dim myFormula As String myVar = TheValue("C:\GssReports", "gssreport MTTR.xlsx", _ "gssreport 1 ", "K6") myFormula = "=ROUNDUP(((" & myVar _ & "-MOD(" & myVar & "-2,7)-DATE($A$2,4,1))/7)+1,0)" 'what cell gets this formula???? ActiveCell.Formula = myFormula End Sub Function TheValue(Path, WorkbookName, Sheet, Addr) As Date 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 ps. John Walkenbach has a routine that can get values from a closed workbook: http://j-walk.com/ss/excel/eee/eee009.txt Look for either: GetDataFromClosedFile or GetValue. George wrote: 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 -- Dave Peterson |
Reply |
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 |