Macro help
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 |
Macro help
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 "andy" wrote in message ... 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 |
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 |
All times are GMT +1. The time now is 12:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com