Prev: Itemize a colum and fill down
Next: Pivot Table from 2 worksheets
From: andy on 10 May 2010 15:51
Hi - I am totally useless at writing macros:

we have a db that uses hrs and not man months as data-
what i have to do is extract a spread sheet from the db which shows
different values in hours eg (shown as 160h) in any columns or rows.

what i would like is to apply a macro that removes the sign h and then
divide the number by 130 which gives me my result in man months - i.e 160 h =
1.2 for every single value of the spread sheet extraction

if any one can advise me ( or create one - if it easy !!) then that would be
fantastic

thanks
From: Wouter HM on 10 May 2010 16:12
On 10 mei, 21:51, andy <a...(a)discussions.microsoft.com> wrote:
> Hi - I am totally useless at writing macros:
>
> we have a db that uses hrs and not man months as data-
> what i have to do is extract  a spread sheet from the db which shows
> different values in hours eg (shown as 160h)   in any columns or rows.
>
> what i would like is to apply  a macro that removes the sign h  and then
> divide the number by 130 which gives me my result in man months - i.e 160 h =
> 1.2 for every single value of the spread sheet extraction
>
> if any one can advise me ( or create one - if it easy !!) then that would be
> fantastic
>
> thanks

Hi Andy,

In Excel 2003 I created:

Sub HoursToManMonth()
Dim rngHours As Range
Dim strValue As String
Dim dblValue As Double
Dim dblMonth As Double
Dim lngDeci As Long

dblMonth = 130 ' Hours per month
lngDeci = 1 ' Number of decimals

For Each rngHours In ActiveSheet.UsedRange
If LCase(Right(rngHours, 1)) = "h" Then
strValue = rngHours.Value
strValue = Left(strValue, Len(strValue) - 1)
dblValue = CDbl(Trim(strValue)) / dblMonth
rngHours.Value = Round(dblValue, lngDeci)
End If
Next
End Sub

HTH,

Wouter
From: Don Guillett on 10 May 2010 16:11
Sub cleanupandmultiplySAS()
mc = "H"
lr = Cells(Rows.Count, mc).End(xlUp).Row
On Error Resume Next
For i = 2 To lr
If LCase(Right(Cells(i, mc), 1)) = "h" Then
MsgBox Left(Cells(i, mc), Len(Cells(i, mc)) - 1)
Cells(i, mc).Value = Left(Cells(i, mc), Len(Cells(i, mc)) - 1) /130
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"andy" <andy(a)discussions.microsoft.com> wrote in message
news:29DB1F04-9F47-434C-A351-5BE0BE63789F(a)microsoft.com...
> Hi - I am totally useless at writing macros:
>
> we have a db that uses hrs and not man months as data-
> what i have to do is extract a spread sheet from the db which shows
> different values in hours eg (shown as 160h) in any columns or rows.
>
> what i would like is to apply a macro that removes the sign h and then
> divide the number by 130 which gives me my result in man months - i.e 160
> h =
> 1.2 for every single value of the spread sheet extraction
>
> if any one can advise me ( or create one - if it easy !!) then that would
> be
> fantastic
>
> thanks

 | 
Pages: 1
Prev: Itemize a colum and fill down
Next: Pivot Table from 2 worksheets