mperrah
11-12-2007, 11:01 PM
This is a worksheet_change procedure I am trying to call like a regular sub. It doesn't fire when I change the values with screen updating off (I think)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.cells.Count > 1 Then Exit Sub
If Target.Row > 1 And _
Target.Column = 2 Then
With Target
.Offset(0, -1).Formula = "=if(C" & .Row & "="""","""",C" & .Row & "&"" ""&B" & .Row & ")"
.Offset(0, 2).Formula = "=IF(C" & .Row & "="""","""",SUM(E" & .Row & ":F" & .Row & "))"
.Offset(0, 3).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Pass=scan_pass),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 4).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Fail=scan_fail),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 5).Formula = "=IF(C" & .Row & "="""","""",IF(OR(E" & .Row & "=0,E" & .Row & "=""0""),""0%"",E" & .Row & "/D" & .Row & "))"
End With
End If
End Sub
This is what I have tried so far...
Sub replaceSummaryFormula()
Dim tg As Range
Dim colA As Range
With Worksheets("Summary")
Set colA = .Range("A2", .cells(.Rows.Count, "A").End(xlUp))
For Each tg In colA
If Not tg Is Nothing Then
Else
With tg
.Offset(0, 0).Formula = "=if(C" & .Row & "="""","""",C" & .Row & "&"" ""&B" & .Row & ")"
.Offset(0, 3).Formula = "=IF(C" & .Row & "="""","""",SUM(E" & .Row & ":F" & .Row & "))"
.Offset(0, 4).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Pass=scan_pass),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 5).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Fail=scan_fail),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 6).Formula = "=IF(C" & .Row & "="""","""",IF(OR(E" & .Row & "=0,E" & .Row & "=""0""),""0%"",E" & .Row & "/D" & .Row & "))"
End With
End If
Next tg
End With
End Sub
any ideas
Thank you in advance
Mark
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.cells.Count > 1 Then Exit Sub
If Target.Row > 1 And _
Target.Column = 2 Then
With Target
.Offset(0, -1).Formula = "=if(C" & .Row & "="""","""",C" & .Row & "&"" ""&B" & .Row & ")"
.Offset(0, 2).Formula = "=IF(C" & .Row & "="""","""",SUM(E" & .Row & ":F" & .Row & "))"
.Offset(0, 3).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Pass=scan_pass),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 4).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Fail=scan_fail),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 5).Formula = "=IF(C" & .Row & "="""","""",IF(OR(E" & .Row & "=0,E" & .Row & "=""0""),""0%"",E" & .Row & "/D" & .Row & "))"
End With
End If
End Sub
This is what I have tried so far...
Sub replaceSummaryFormula()
Dim tg As Range
Dim colA As Range
With Worksheets("Summary")
Set colA = .Range("A2", .cells(.Rows.Count, "A").End(xlUp))
For Each tg In colA
If Not tg Is Nothing Then
Else
With tg
.Offset(0, 0).Formula = "=if(C" & .Row & "="""","""",C" & .Row & "&"" ""&B" & .Row & ")"
.Offset(0, 3).Formula = "=IF(C" & .Row & "="""","""",SUM(E" & .Row & ":F" & .Row & "))"
.Offset(0, 4).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Pass=scan_pass),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 5).Formula = "=IF(C" & .Row & "="""","""",SUMPRODUCT(--(Techs=$C" & .Row & "),--(Fail=scan_fail),--(QCDate>=wqcStart),--(QCDate<=wqcEnd)))"
.Offset(0, 6).Formula = "=IF(C" & .Row & "="""","""",IF(OR(E" & .Row & "=0,E" & .Row & "=""0""),""0%"",E" & .Row & "/D" & .Row & "))"
End With
End If
Next tg
End With
End Sub
any ideas
Thank you in advance
Mark