#### madchemist

##### Board Regular

- Joined
- Jul 10, 2006

- Messages
- 198

Does anyone have any idea why it would do this? I really need to get around this problem. This thing works so beautifully minus this issue. Thanks!

Code:

```
Application.ScreenUpdating = False
Sheets(PIA).Select
[H2].Select
Selection.NumberFormat = "@"
frmAdd.Show
[B2].FormulaR1C1 = PIA
Sheets("Action Item Trends").Select
[C9].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C10].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C11].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],2.06)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C12].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C13].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C14].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C15].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C16].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C17].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C18].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.05)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C19].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C20].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C21].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],7.08)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C22].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C23].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C24].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],9.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C25].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
[C26].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],10.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
[C43].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Open""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Open"")"
[C44].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Closed""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"
[C45].FormulaR1C1 = "=COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"
Sheets(PIA).Select
[C2].Select
```

Code from frmadd

Code:

```
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Rows.AutoFit
[H2].Select
Selection.NumberFormat = "@"
Range("A2:D2,F2:J2,L2:P2,R2:S2").Select
Selection.Locked = False
Selection.FormulaHidden = False
[C2].Select
ActiveCell.Value = txtSubject.Value
ActiveCell.Offset(0, 1) = txtDescription.Value
ActiveCell.Offset(0, 3) = txtDCF.Value
ActiveCell.Offset(0, 9) = Application.UserName
ActiveCell.Offset(0, 4) = ComboBox_Responsible.Value
ActiveCell.Offset(0, 5) = txtCode.Value
ActiveCell.Offset(0, 6) = ComboBox_Severity.Value
ActiveCell.Offset(0, 7) = ComboBox_Frequency.Value
ActiveCell.Offset(0, 10) = txtDue.Value
ActiveCell.Offset(0, 16) = txtComments.Value
Range("A2:S2").Select
Selection.Font.Bold = False
Selection.Interior.ColorIndex = xlNone
With Selection.Font
.Name = "Arial"
.Size = 8
End With
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
End With
[A2:C2,E2:R2].Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
End With
[D2,S2].Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
End With
[A2:M2:N2:R2].Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
[B2:L2,O2:Q2,S2].Select
Selection.NumberFormat = "General"
[H2].Select
Selection.NumberFormat = "@"
[K2].FormulaR1C1 = "=INDEX({2,3,5},1,MATCH(RC[-2],{""Low"",""Mod"",""High""},0))*INDEX({1,2,3},1,MATCH(RC[-1],{""Low"",""Mod"",""High""},0))"
[P2].FormulaR1C1 = "Open"
[P2].Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
[Q2].FormulaR1C1 = "=NETWORKDAYS(RC[-16],IF(RC[-1]=""open"",TODAY(),IF(RC[-1]=""escalated"",RC[1],RC[-3])))"
[A2].FormulaR1C1 = "=TODAY()"
[A2].Select
Selection.Copy
[A2].Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
[K2].Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="9"
Selection.FormatConditions(1).Interior.ColorIndex = 7
[H2].Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(H2)"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Casegenerater
Unload Me
```

Code from Casegenerater

Code:

```
Sub Casegenerater()
Prefix = Strings.Left(ActiveSheet.Name, 4)
Count = 0
For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row
If Strings.Left(Cells(i, "E"), 4) = Prefix Then Count = Count + 1
Next i
CaseCount = Count + 1
Cells(2, "E").Value = Prefix & "-" & Format(CaseCount, "0000")
End Sub
```