Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation
I am doing a worksheet that draws data from Access. I need to separate this
data based on a any of 8 dates being within the report month. I pull the data into one sheet, then on a separate (monthly) sheet I am writing a function which will write in the name of the client that has one (or many) of the dates in the report month. I have several problems. Excel 2000 is starting to calculate from the bottom of the worksheet, not the top and there appears to be no switch to tell it to start from the upper left hand corner (nothing at the upper left sides needs anything down and right of the calling cell). This, of course means that I have to know the exact number of clients that month and only populate that many rows with the function calls. Not very good planning and it also gives me a reverse alphabetical listing of the clients. I have tried several ways to get around this, but none works. I can't write to another cell from a VB function. Setting a global doesn't work since the Workbook_Calculate sub is not always called - but I finally figured out a way around that problem for the first sheet, having gotten to the next month, yet. Thanks for any assistance. John H W |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation
What does your function look like?
Tim -- Tim Williams Palo Alto, CA "John H W" wrote in message ... I am doing a worksheet that draws data from Access. I need to separate this data based on a any of 8 dates being within the report month. I pull the data into one sheet, then on a separate (monthly) sheet I am writing a function which will write in the name of the client that has one (or many) of the dates in the report month. I have several problems. Excel 2000 is starting to calculate from the bottom of the worksheet, not the top and there appears to be no switch to tell it to start from the upper left hand corner (nothing at the upper left sides needs anything down and right of the calling cell). This, of course means that I have to know the exact number of clients that month and only populate that many rows with the function calls. Not very good planning and it also gives me a reverse alphabetical listing of the clients. I have tried several ways to get around this, but none works. I can't write to another cell from a VB function. Setting a global doesn't work since the Workbook_Calculate sub is not always called - but I finally figured out a way around that problem for the first sheet, having gotten to the next month, yet. Thanks for any assistance. John H W |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation
Tim - Here is the functions
In each Row starting at row 3: Col A =GetName("1/1/2005") Col B =IF(A3"", GetJASID(A3),"") Col C through O =IF(A3"", GetDate(C2),"") Note: I have not included GetDate below -------------------- Option Explicit Dim gnCurrentRow As Integer Dim gbLastRowHit As Boolean, gbRunning As Boolean Public Function GetName(dtReportMonth As Date) As String Dim strCell As String, nRow As Integer, nCurrentRow As Integer Dim strTemp As String Dim bFound As Boolean, strRow As String Dim strReport As String, vTemp As Variant, strName As String If Not gbRunning Then gnCurrentRow = 3 gbLastRowHit = False gbRunning = True Else gnCurrentRow = gnCurrentRow + 1 End If If gbLastRowHit Then GetName = "" gbRunning = False Exit Function End If nRow = gnCurrentRow bFound = False With Worksheets("Data") strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If IsEmpty(strName) Then gbLastRowHit = True GetName = "" Exit Function End If While Not bFound strCell = "C" + strRow + ":L" + strRow For Each vTemp In .Range(strCell) If vTemp.Value = Empty Then ElseIf DateInMonth(CDate(vTemp), dtReportMonth) Then bFound = True Exit For End If Next If bFound Then gnCurrentRow = nRow GetName = strName Else nRow = nRow + 1 strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If IsEmpty(strName) Then gbLastRowHit = True gnCurrentRow = nRow GetName = "" Exit Function End If End If Wend End With End Function Public Function GetJASID(ByVal Name As String) As String Dim nRow As Integer, bFound As Boolean, strRow As String Dim strCell As String, strName As String nRow = 3 bFound = False With Worksheets("Data") While Not bFound strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If strName = Name Then bFound = True Else nRow = nRow + 1 End If Wend If bFound Then strCell = "B" + strRow GetJASID = .Range(strCell) Else GetJASID = "" End If End With End Function Public Function DateInMonth(ByVal dtIntro As Date, ByVal dtMonth As Date) As Boolean Dim nMonth As Integer, nYear As Integer Dim nIntroMonth As Integer, nIntroYear As Integer nMonth = Month(dtMonth) nYear = Year(dtMonth) nIntroMonth = Month(dtIntro) nIntroYear = Year(dtIntro) If nMonth = nIntroMonth And nYear = nIntroYear Then DateInMonth = True Else DateInMonth = False End If End Function ------------------------------- "Tim Williams" wrote: What does your function look like? Tim -- Tim Williams Palo Alto, CA "John H W" wrote in message ... I am doing a worksheet that draws data from Access. I need to separate this data based on a any of 8 dates being within the report month. I pull the data into one sheet, then on a separate (monthly) sheet I am writing a function which will write in the name of the client that has one (or many) of the dates in the report month. I have several problems. Excel 2000 is starting to calculate from the bottom of the worksheet, not the top and there appears to be no switch to tell it to start from the upper left hand corner (nothing at the upper left sides needs anything down and right of the calling cell). This, of course means that I have to know the exact number of clients that month and only populate that many rows with the function calls. Not very good planning and it also gives me a reverse alphabetical listing of the clients. I have tried several ways to get around this, but none works. I can't write to another cell from a VB function. Setting a global doesn't work since the Workbook_Calculate sub is not always called - but I finally figured out a way around that problem for the first sheet, having gotten to the next month, yet. Thanks for any assistance. John H W |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation
Your function "GetName" seems to be the problem. It's not really a good
idea to structure a UDF like this - it would be better to place this kind of calculation in a sub called from the worksheet_change event or similar. Tim -- Tim Williams Palo Alto, CA "John H W" wrote in message ... Tim - Here is the functions In each Row starting at row 3: Col A =GetName("1/1/2005") Col B =IF(A3"", GetJASID(A3),"") Col C through O =IF(A3"", GetDate(C2),"") Note: I have not included GetDate below -------------------- Option Explicit Dim gnCurrentRow As Integer Dim gbLastRowHit As Boolean, gbRunning As Boolean Public Function GetName(dtReportMonth As Date) As String Dim strCell As String, nRow As Integer, nCurrentRow As Integer Dim strTemp As String Dim bFound As Boolean, strRow As String Dim strReport As String, vTemp As Variant, strName As String If Not gbRunning Then gnCurrentRow = 3 gbLastRowHit = False gbRunning = True Else gnCurrentRow = gnCurrentRow + 1 End If If gbLastRowHit Then GetName = "" gbRunning = False Exit Function End If nRow = gnCurrentRow bFound = False With Worksheets("Data") strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If IsEmpty(strName) Then gbLastRowHit = True GetName = "" Exit Function End If While Not bFound strCell = "C" + strRow + ":L" + strRow For Each vTemp In .Range(strCell) If vTemp.Value = Empty Then ElseIf DateInMonth(CDate(vTemp), dtReportMonth) Then bFound = True Exit For End If Next If bFound Then gnCurrentRow = nRow GetName = strName Else nRow = nRow + 1 strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If IsEmpty(strName) Then gbLastRowHit = True gnCurrentRow = nRow GetName = "" Exit Function End If End If Wend End With End Function Public Function GetJASID(ByVal Name As String) As String Dim nRow As Integer, bFound As Boolean, strRow As String Dim strCell As String, strName As String nRow = 3 bFound = False With Worksheets("Data") While Not bFound strRow = CStr(nRow) strCell = "A" + strRow strName = .Range(strCell) If strName = Name Then bFound = True Else nRow = nRow + 1 End If Wend If bFound Then strCell = "B" + strRow GetJASID = .Range(strCell) Else GetJASID = "" End If End With End Function Public Function DateInMonth(ByVal dtIntro As Date, ByVal dtMonth As Date) As Boolean Dim nMonth As Integer, nYear As Integer Dim nIntroMonth As Integer, nIntroYear As Integer nMonth = Month(dtMonth) nYear = Year(dtMonth) nIntroMonth = Month(dtIntro) nIntroYear = Year(dtIntro) If nMonth = nIntroMonth And nYear = nIntroYear Then DateInMonth = True Else DateInMonth = False End If End Function ------------------------------- "Tim Williams" wrote: What does your function look like? Tim -- Tim Williams Palo Alto, CA "John H W" wrote in message ... I am doing a worksheet that draws data from Access. I need to separate this data based on a any of 8 dates being within the report month. I pull the data into one sheet, then on a separate (monthly) sheet I am writing a function which will write in the name of the client that has one (or many) of the dates in the report month. I have several problems. Excel 2000 is starting to calculate from the bottom of the worksheet, not the top and there appears to be no switch to tell it to start from the upper left hand corner (nothing at the upper left sides needs anything down and right of the calling cell). This, of course means that I have to know the exact number of clients that month and only populate that many rows with the function calls. Not very good planning and it also gives me a reverse alphabetical listing of the clients. I have tried several ways to get around this, but none works. I can't write to another cell from a VB function. Setting a global doesn't work since the Workbook_Calculate sub is not always called - but I finally figured out a way around that problem for the first sheet, having gotten to the next month, yet. Thanks for any assistance. John H W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculation order | Excel Worksheet Functions | |||
Order of calculation | Excel Discussion (Misc queries) | |||
Order of calculation | Excel Discussion (Misc queries) | |||
How do I set the order of calculation of Data Tables in Excel? | Excel Worksheet Functions | |||
BIMDAS - Order of Calculation | Excel Discussion (Misc queries) |