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(D3N3,"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

Hi

I'm not sure how it'll work, but you can try
=addtimeb(Sheet1!C3:CN3,"H") on Sheet1;
=addtimeb(Sheet2!C3:CN3,"H") on Sheet2;
etc.


Arvi Laanemets


"PK" kirjutas sõnumis news:
...
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(D3N3,"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

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(D3N3,"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

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(D3N3,"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

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(D3N3,"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

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(D3N3,"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

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(D3N3,"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

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(D3N3,"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

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(D3N3,"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