Macro help
On 10 mei, 21:51, andy 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
|