| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function updating all worksheets in workbook
I have a function in VBA as detailed at the end of my text and it is updating
all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
PK,
I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
Really sorry for making it all sound so confusing. Unable to upload my file
due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C3 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (emplyee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
Hi,
Change this line If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout to this and test it If c.Offset(-(c.Row - 2)).Value Range("A2").Value Then GoTo getmeout -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Really sorry for making it all sound so confusing. Unable to upload my file due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C3 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (emplyee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
Oh yes Oh yes Oh yes !!!! It works!
You have made my week! Thanks Mike. Appreciate your time and patience. -- PK wilts "Mike H" wrote: Hi, Change this line If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout to this and test it If c.Offset(-(c.Row - 2)).Value Range("A2").Value Then GoTo getmeout -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Really sorry for making it all sound so confusing. Unable to upload my file due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C3 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (emplyee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
We got there in the end, Glad I could help and thanks for the feedback. have
a look at my reply in your other post to understand how it works -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Oh yes Oh yes Oh yes !!!! It works! You have made my week! Thanks Mike. Appreciate your time and patience. -- PK wilts "Mike H" wrote: Hi, Change this line If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout to this and test it If c.Offset(-(c.Row - 2)).Value Range("A2").Value Then GoTo getmeout -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Really sorry for making it all sound so confusing. Unable to upload my file due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C3 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (emplyee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
Ignore last reply I made another error on line 3. Should read C2 not C3. I am
having a mare on this query. Really sorry for making it all sound so confusing. Unable to upload my file due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C2 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (employee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function updating all worksheets in workbook
Look at my previous response.
You have correctly identified the bug in the code in that it would always use the active sheet data range for calculating the sum. Specifying the date range using OFFSET should overcome this problem. For example (say) we are evaluating D10 or row 10. The cell being evaluated in the code is called C and we need to get the date value from a cell offset 8 rows up (I.E row 2) The new code of c.Offset(-(c.Row - 2)) evaluates to c.offset(-(10-2) or c.offset(-8) so whatever row the code is in it now gets its date value from row 2 on the same sheet. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: Ignore last reply I made another error on line 3. Should read C2 not C3. I am having a mare on this query. Really sorry for making it all sound so confusing. Unable to upload my file due to my company websense restrictions. Will try and explain again. My workbook has 12 sheets, one for each month with row 2 starting at cell C2 always being a consecutive date field. (just realised I previously said column 2 oops sorry - that would make little sense to you). Row 2 is the only row that differs on each worksheet. Cell A2 on each worksheet is always the current system date. Column 1 and 2 are details against emplyees (employee number and section). Data is input into cells C3:AD82 (there are 80 emplyees) . The code you sent me on the summing of holiday (H) booked and lieutime (L) booked works perfectly. It's just the coding I have sent earlier (function addtimeb)whereby I need to calculate holiday (H) taken by each emplyee which is calculated where the Row 2 date is <Cell A2 (ie in the past). When I update any sheet with data all sheets in the workbook are recalculated (which is not a problem) but all recalculations point to Row 2 of the sheet I am updating as their date criteria source not Row 2 of each individual worksheet for its own specific worksheet. I hope having realised my error in calling row 2 column 2 things may make more sense - sorry. I do hope so as this last bit is so key to what I am trying to achieve. I am so grateful to you thus far. -- PK wilts "Mike H" wrote: PK, I'm no closer to understanding your data layout and what your trying to achieve than I was yesterday when I gave you this function. In particular, I don't understand this A2). However when I amend ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. If you upload your workbook to the site below and post the link and in the workbook give a 'precise' description of what you require I'll take a look at it. http://rapidshare.com/ -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PK" wrote: I have a function in VBA as detailed at the end of my text and it is updating all worksheets in a workbook rather than just the active one. I have 12 worksheets all identical apart from Column 2 which holds consecutive date data (hence one sheet per month). Cell A2 shows the current date on all worksheets. My cell formula on each worksheet is =addtimeb(C3:CN3,"H") which changes for each subsequent row for a number of rows ie =addtimeb(D3:DN3,"H") =addtimeb(E3:EN3,"H") =addtimeb(F3:FN3,"H") etc etc each row is an employee record. I am extracting and summing numbers, from string text fields, that follow a "H" (for holiday) and where column 2 has a date <= the current date (cell A2). However when I amendd ata on any sheet it automatically updates all sheets but only takes into account the dates in column 2 on the active sheet. Hence data held on the remaining 11 sheets becomes inaccurate. Function addtimeb(rng As Range, ltr As String) Application.Volatile Dim ts As String Dim x As Long Dim DateRow As Long ltr = UCase(ltr) For Each c In rng If Cells(2, c.Column).Value Range("A2").Value Then GoTo getmeout If InStr(UCase(c.Value), ltr) 0 Then For x = InStr(UCase(c.Value), ltr) To Len(c.Value) If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then ts = ts + Mid(c.Value, x, 1) If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _ Mid(c.Value, x + 1, 1) < "." Then Exit For End If Next End If If ts < "" Then addtimeb = addtimeb + Val(ts) ts = "" End If getmeout: Next End Function Thank you. -- PK wilts |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUM function across multiple worksheets within one workbook | Excel Worksheet Functions | |||
| My workbook links are not updating (its 30,000 KB size workbook). | Excel Discussion (Misc queries) | |||
| Auto updating a workbook with data from another workbook | Excel Discussion (Misc queries) | |||
| Updating multiple worksheets in a large workbook | Excel Discussion (Misc queries) | |||
| updating excel worksheets to another workbook | Excel Worksheet Functions | |||
Hybrid Mode
