• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba macro code

T

Tauqeer

Member
Hi

I need help to adjust these macros, it’s a time sheet which records events and the time it took to resolve them.

I have attached the file which has 02 sheets in it, one is the SLA and the other is the sample data,

Cut and paste the data from data sheet to SLA sheet, press the SLA button on the right , it adds the

Col f as SLA , calculate the difference between D and E col, format it into H:mm and then do conditional formatting and shows cells as red if the time is over 1:00 and Green cells if the time is less than 1:00. Clear button removes all data contents and the extra SLA col and back to empty file, so need to paste the data again to perform the same task, as data changes every day so I can paste new data to perform the same task.

As the data is different every day, sometime it has 15 transactions, sometimes 100 , so the col f shows SLA as green even if there is no data in the bottom, question is , is there any way it wont add those green col in the bottom and fluctuate depends on the data if that macro is possible or I can edit the SLA macro. Please advise.

I am uploading the file as well.

thanks

Tauqeer
 

Attachments

Tauqeer

Change the module SLAFINAL to that shown below

Code: 
Sub SLAFINAL()
'
' SLAFINAL Macro
'

'
  Columns("F:F").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("Table3[[#Headers],[Column1]]").Select
  Selection.FormulaR1C1 = "SLA"
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("F2").Select
  ActiveCell.FormulaR1C1 = _
  "=[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]"
  Range("F2").Select
  Selection.NumberFormat = "h:mm"
  Selection.AutoFill Destination:=Range("Table3[SLA]")
  Range("Table3[SLA]").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  Formula1:="=0.0416666666666667"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16383844
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13551615
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
  Formula1:="=0.0416666666666667"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16752384
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13561798
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Range("F2").Select

' New code below
Dim lastRow As Integer
Dim FirstRow As Integer

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("E" & lastRow).Select
Range(Selection, Selection.End(xlUp)).Offset(1).Select
Selection.Offset(0, 1).Select


FirstRow = Selection.Row

Selection.ClearContents
Range("E" & FirstRow & ":E" & lastRow).Select

Selection.Copy Range("F" & FirstRow & ":F" & lastRow)

Application.CutCopyMode = False

Range("F2").Select

  
End Sub
 
Hui said:
Tauqeer

Change the module SLAFINAL to that shown below

Code: 
Sub SLAFINAL()
'
' SLAFINAL Macro
'

'
  Columns("F:F").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("Table3[[#Headers],[Column1]]").Select
  Selection.FormulaR1C1 = "SLA"
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("F2").Select
  ActiveCell.FormulaR1C1 = _
  "=[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]"
  Range("F2").Select
  Selection.NumberFormat = "h:mm"
  Selection.AutoFill Destination:=Range("Table3[SLA]")
  Range("Table3[SLA]").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  Formula1:="=0.0416666666666667"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16383844
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13551615
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
  Formula1:="=0.0416666666666667"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16752384
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13561798
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Range("F2").Select

' New code below
Dim lastRow As Integer
Dim FirstRow As Integer

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("E" & lastRow).Select
Range(Selection, Selection.End(xlUp)).Offset(1).Select
Selection.Offset(0, 1).Select


FirstRow = Selection.Row

Selection.ClearContents
Range("E" & FirstRow & ":E" & lastRow).Select

Selection.Copy Range("F" & FirstRow & ":F" & lastRow)

Application.CutCopyMode = False

Range("F2").Select

 
End Sub
Click to expand...
 
Hi Hui

Thanks a lot for solving my problem, one more favour I need to ask, if
I press the macro button SLA twice accidently then I will create an another col or if i accidently press clear twice then I will delete the extra column, is it possible somehow to avoid this or a check can be placed in the macro that pressing the SLA or clear button twice wont work or any other solution you would suggest.
please advise.


regards

Tauqeer
 
Hi Hui


Macro code works fine with the daily data , but when I put the monthly data in the SLA sheet which is bigger data then somehow the marco breaks, please advise if there is any adjustment needed for monthly data. I am attaching the sheet with Monthly and daily data as an example.


Thanks


Tauqeer
 

Attachments

You must log in or register to reply here.
Back
Top