This will sound dumb I know but does your second piece of VBA also accomodate
my first request? ie can I use this coding to calculate holiday booked (ie my
first request) and holiday taken (ie my second request) in two separate
result cells? Also if I need to replace the first piece of code you sent me
how do I open the existing code within VB? I am a total novice in VB and as I
say prob a dumb question but please let me know anyway :-)
--
PK wilts


"Mike H" wrote:

Ah,

I understand what your saying. Your validation date is 'always' in row 2 but
the holiday coluld be in any row so if we are in row 9 we still colpare the
date in row 2 to the date in a2. Try this instead:-

Function addtime(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
addtime = addtime + Val(ts)
ts = ""
End If
getmeout:
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

The formula you posted won't work because the ranges are different lengths.
I think your saying that if the date in the row above the string is 'in the
future then ignore it. Try this

Function addtime(rng As Range, ltr As String)
Dim ts As String
Dim x As Long
ltr = UCase(ltr)
For Each c In rng
If c.Offset(-1, 0) 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
addtime = addtime + Val(ts)
ts = ""
End If
getmeout:
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PK" wrote:

Hi Mike,
Wow!! That works perfectly. I have a follow on question though.
I have a second formula that calculates if the "holiday" has been taken
taking into account the current date (which is held in cell A2) and if it has
passed my column header dates held in row 2 from C2 onwards. This formula is
as follows:
=SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2)))
How do I amend this to incorporate your =addtime(C3:CN3,"H") formula?
Currently its giving me a VALUE error bcause of the merging in one cell of
for example H3.5L4.5.
Thanks.
--
PK wilts


"Mike H" wrote:

Hi,

How about a user defined function. Alt+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in

call the code with

=addtime(rng,"L")

where rng is the range to count and "L" is the letter to sum so for your
example

=addtime(C3:G3,"h")


Function addtime(rng As Range, ltr As String)
Dim ts As String
Dim x As Long
ltr = UCase(ltr)
For Each c In rng
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
addtime = addtime + Val(ts)
ts = ""
End If
Next
End Function

--
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 various cells some of which hold an alpha-number field, some hold an
alpha-number-alpha-number field, some are blank. These range for example from
cells C3:G3 as follows starting at C3
H7.5 blank H3.5L4.5 blank L7.5
I would like to sum all numbers prefixed with H and seprately, all prefixed
with L
Hence the result from above should read - Total for H is 11 Total for L is
12.
I have 2 separate formulas as follows which work until you input an H value
and an L value into the same field.
=SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1))
=SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1))
I am aware this is an array formula and one must complete the shift control
enter to obtain curved brackets around the formula.
Any assistance to solve my issue would be very much appreciated.
For info H = holiday taken. L = Lieu time taken.
On some days the staff merge H with acquired L to make up a day off.
Thanks,
PK
--
PK wilts