Looping worksheet Problem

[Dahlia]

Hye,

I have tried to do this but I still being prompted with object defined error. Supposedly, it loops all visible worksheets only except for the "All" sheet (action button I put here). Here are my codes:-

Sub UpdateCharts()
Dim s As Worksheet


For Each s In ThisWorkbook.Worksheets
If s.Name<>"All" and s.Name.visible=TRUE Then Call RankMe
Next s
End Sub

Sub CalculateMe()
Dim ERow As Integer
Dim SRow As Integer
Dim OldRow As Integer
Application.ScreenUpdating = False


OldRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row


SRow = ActiveSheet.Range("A2").Value
ERow = ActiveSheet.Range("A3").Value


If SRow = OldRow Then
ActiveSheet.Range("D" & SRow & ":F" & SRow).ClearContents
Else
ActiveSheet.Range("D" & SRow & ":F" & OldRow).ClearContents
End If
ActiveSheet.Range("D" & SRow).FormulaR1C1 = "=IF(RC2<>""-"",RC3+10,"")
Application.ScreenUpdating = True
End Sub

Can someone help to correct what am doing wrong here, please? Cause I've been cracking my head to try all sorts of tweaks but to no avail. However this works perfectly when running on one by one sheet. But I need to be able to do it for simultaneously from one sheet named "All". Am getting desperate now as I need to demo this tomorrow.

Thank you in advance.
DZ

 

[Hui]

Should your line
If s.Name<>"All" and s.Name.visible=TRUE Then Call RankMe
be:
If s.Name<>"All" and s.Name.visible=TRUE Then Call CalculateMe

 

[Dahlia]

Sorry Hui,
The one I posted here is a sample out of the full lengthy coding which named originally as RankMe. But it always pop up error when starting to do the IF THEN ELSE codes. I believe this is not the coding inside that is wrong, because it works successfully when running it on one by one worksheet. It must be my looping coding is wrong somewhere.

Please help..

 

[shrivallabha]

I suspect that it is looping on chart-sheet and it is tripping there.

If you are sure that it runs into the error while executing the code snippet posted above then try like below.

Sub CalculateMe()
Dim ERow As Integer
Dim SRow As Integer
Dim OldRow As Integer
Application.ScreenUpdating = False
'\\ Instead of breaking goto error flag
On Error GoTo Errorflag

OldRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row


SRow = ActiveSheet.Range("A2").Value
ERow = ActiveSheet.Range("A3").Value


If SRow = OldRow Then
ActiveSheet.Range("D" & SRow & ":F" & SRow).ClearContents
Else
ActiveSheet.Range("D" & SRow & ":F" & OldRow).ClearContents
End If
ActiveSheet.Range("D" & SRow).FormulaR1C1 = "=IF(RC2<>""-"",RC3+10,"")"
'\\ Handle for error
Errorflag:
If Err.Number <> 0 Then
    MsgBox "Getting Error on : " & ActiveSheet.Name, vbInformation
End If
Application.ScreenUpdating = True
End Sub

If it gets an error instead of breaking it will give you onscreen message on the failing sheet.

 

[NARAYANK991]

Hi ,

There is an error in the usage of :

If s.Name<>"All"and s.Name.visible=TRUE Then Call RankMe

Remember , when ever you type in code in the VBE , all keywords will have their first letter capitalized , as for example the N in Name. The V in visible will also be capitalized if the usage is correct.

Try the following :

If ((s.Name<>"All") and (s.Visible=TRUE)) Then Call RankMe

Narayan