Please Tell me what is wrong with this Code

[Siyanna]

Please tell me where i am going wrong. Thank You So Much

Sub UpdateTotal()
'Update Scores
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Lrow As Long
Dim Rng As Range
Dim Lcol As Long
Set Sh = Sheets("Table")
Lrow = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & Lrow)
Lcol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column + 1
For Each WS In Worksheets
    If WS.Name <> "Table" And WS.Name <> "Result" Then
        [COLOR=#ff0000]With Range(Rng)
          .Offset(0, 1).Formula = "=IF(AND(B2=Result!B2,D2=Result!D2),3,IF(AND(B2=D2,Result!B2=Result!D2),1,IF(OR(AND(D2>B2,Result!D2>Result!B2),AND(B2>D2,Result!B2>Result!D2)),1,0)))"
          .Value = .Value
[/COLOR]       End With
    End If
Next WS
'Update Summary Sheet
With Sh.Range(Cells(2, Lcol), Cells(27, Lcol))
   .Formula = "=INDIRECT(""'""&A2&""'!""&""F""&MATCH(1E+100,Result!F:F))"
   .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[Michael M]

try

Sub UpdateTotal()
'Update Scores
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Lrow As Long, Rng As Range, Lcol As Long, ws As Worksheet
Set Sh = Sheets("Table")
Lrow = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & Lrow)
Lcol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column + 1
For Each ws In Worksheets
    If ws.Name <> "Table" And ws.Name <> "Result" Then
        With Rng
          .Offset(0, 1).Formula = "=IF(AND(B2=Result!B2,D2=Result!D2),3,IF(AND(B2=D2,Result!B2=Result!D2),1,IF(OR(AND(D2>B2,Result!D2>Result!B2),AND(B2>D2,Result!B2>Result!D2)),1,0)))"
          .Value = .Value
       End With
    End If
Next ws
'Update Summary Sheet
With Sh.Range(Cells(2, Lcol), Cells(27, Lcol))
   .Formula = "=INDIRECT(""'""&A2&""'!""&""F""&MATCH(1E+100,Result!F:F))"
   .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[mahmed1]

Please tell me where i am going wrong. Thank You So Much

Sub UpdateTotal()
'Update Scores
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Lrow As Long
Dim Rng As Range
Dim Lcol As Long
Set Sh = Sheets("Table")
Lrow = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & Lrow)
Lcol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column + 1
For Each WS In Worksheets
    If WS.Name <> "Table" And WS.Name <> "Result" Then
        [COLOR=#ff0000]With Range(Rng)
          .Offset(0, 1).Formula = "=IF(AND(B2=Result!B2,D2=Result!D2),3,IF(AND(B2=D2,Result!B2=Result!D2),1,IF(OR(AND(D2>B2,Result!D2>Result!B2),AND(B2>D2,Result!B2>Result!D2)),1,0)))"
          .Value = .Value
[/COLOR]      End With
    End If
Next WS
'Update Summary Sheet
With Sh.Range(Cells(2, Lcol), Cells(27, Lcol))
   .Formula = "=INDIRECT(""'""&A2&""'!""&""F""&MATCH(1E+100,Result!F:F))"
   .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Thank You

For some reason it is not skipping over the sheets that i have tried to eliminate with the If statement.

 

[Siyanna]

Thank You

For some reason it is not skipping over the sheets that i have tried to eliminate with the If statement.

Hi Mahmed1

Is there something similiar you are dealing with?

I have tried to activate the sheet but i guess Michael will be able to help better than me.

Thanks

 

[Siyanna]

Hi Michael

Why is it not skipping over the sheets when trying to input the formula?

Please advise where i am going wrong

Lrow = Sheets("Result").Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A2:A" & Lrow)

WS.Activate
With Rng
.Offset(0, 5).Formula = "=IF(AND(Result!B2="""",Result!D2=""""),"""",IF(AND(Result!B2=""P"",Result!D2=""P""),""P"",IF(AND(B2=Result!B2,D2=Result!D2),3,IF(AND(B2=D2,Result!B2=Result!D2),1,IF(OR(AND(D2>B2,Result!D2>Result!B2),AND(B2>D2,Result!B2>Result!D2)),1,0)))))"
.Value = .Value
End With

Thank You

 

[Michael M]

Try activating the WS

Sub UpdateTotal()
'Update Scores
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Lrow As Long, Rng As Range, Lcol As Long, ws As Worksheet
Set Sh = Sheets("Table")
Lrow = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & Lrow)
Lcol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column + 1
For Each ws In Worksheets
    If ws.Name <> "Table" And ws.Name <> "Result" Then
        ws.Activate
        With Rng
          .Offset(0, 1).Formula = "=IF(AND(B2=Result!B2,D2=Result!D2),3,IF(AND(B2=D2,Result!B2=Result!D2),1,IF(OR(AND(D2>B2,Result!D2>Result!B2),AND(B2>D2,Result!B2>Result!D2)),1,0)))"
          .Value = .Value
       End With
    End If
Next ws
'Update Summary Sheet
With Sh.Range(Cells(2, Lcol), Cells(27, Lcol))
   .Formula = "=INDIRECT(""'""&A2&""'!""&""F""&MATCH(1E+100,Result!F:F))"
   .Value = .Value
End With
Application.ScreenUpdating = True
End Sub