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