Code to Loop through Each Worksheet in ThisWorkbook.Worksheets

[xcelnovice101]

I'm hoping this is an easy fix. The macro will not select the next sheet "ws" in ThisWorkbook. The message I get on the highlighted line of code is "Method 'Select' of object'_Worksheet' failed" on the attempt at a second run through the code; everything works fine tthr first time through.

'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            ws.Select
            
'CREATE HEADER
            ws.[J6] = "Price"
            ws.[K6] = "% Change"
            ws.[L7] = "Company"
            ws.[M7] = "Industry"
            ws.[N7] = "Sector"
            ws.Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            ws.Range(Range("J" & CHEMICALS), Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            ws.Range(Range("K" & CHEMICALS), Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            ws.Range(Range("L" & CHEMICALS), Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            ws.Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & Range(Range("L" & CHEMICALS), Range("L" & COAL)).Address(False, False) & ")"
        End With
    Next ws

[ArnolddG]

I think you would not need to use

ws.select

but

.select

Since now it is read as

ws.ws.select

[xcelnovice101]

I think you would not need to use

ws.select

but

.select

Since now it is read as

ws.ws.select

I'm getting the same error message with this.

Below is the section of code I changed.

'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .Select
            
'CREATE HEADER
            ws.[J6] = "Price"
            ws.[K6] = "% Change"
            ws.[L7] = "Company"
            ws.[M7] = "Industry"
            ws.[N7] = "Sector"
            ws.Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart

[stnkynts]

Remove the With ws and the End With lines

[xcelnovice101]

Remove the With ws and the End With lines

I'm getting the same error message here as well.

Below are the sections of code I changed.

'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
            ws.Select
            
'CREATE HEADER
            ws.[J6] = "Price"
            ws.[K6] = "% Change"
            ws.[L7] = "Company"
            ws.[M7] = "Industry"
            ws.[N7] = "Sector"
            ws.Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart

    
    Next ws

Application.ScreenUpdating = True

[AB33]

'Option Explicit
Sub loopthrough()


'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            
            
'CREATE HEADER
            .[J6] = "Price"
            .[K6] = "% Change"
            .[L7] = "Company"
            .[M7] = "Industry"
            .[N7] = "Sector"
            .Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(.Range("K" & CHEMICALS), .Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
        End With
    Next ws
End Sub

[xcelnovice101]

'Option Explicit
Sub loopthrough()


'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            
            
'CREATE HEADER
            .[J6] = "Price"
            .[K6] = "% Change"
            .[L7] = "Company"
            .[M7] = "Industry"
            .[N7] = "Sector"
            .Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(.Range("K" & CHEMICALS), .Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
        End With
    Next ws
End Sub

I'm now receiving the following message at the highlighted line of code. "Method 'Range' of object'_Worksheet' failed"

'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
            With ws
            
'CREATE HEADER
            .[J6] = "Price"
            .[K6] = "% Change"
            .[L7] = "Company"
            .[M7] = "Industry"
            .[N7] = "Sector"
            .Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(Range("J" & CHEMICALS), Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(Range("K" & CHEMICALS), Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(Range("L" & CHEMICALS), Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & Range(Range("L" & CHEMICALS), Range("L" & COAL)).Address(False, False) & ")"

[AB33]

Please do not reply with quote.

.Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"

A dots are missing. Check my code

[xcelnovice101]

Great! Now, I'm debugging at the below edited line of code. I assumed I needed to add the dots in front of "Range" here as well. This works for the first pass but on the second loop it's debugging. It also appears to be creating the Ranges based on the first sheet (rather than the second) in the second loop so I don't think it's actually going to the "Next ws"

            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"

[AB33]

.Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"

If it works for the first sheet, it is because it found "CHEMICALS". If it does not found, it will error.
Please note this is not a complete code, just merely to give you an idea how to set the find function.

Option Explicit

Sub etst()
Dim findme As Range

With ws.Columns("C:C")
   Set findme = .Find(What:="CHEMICALS")
         If Not findme Is Nothing Then
           .Offset(0, 10).Formula = _
            "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
          End If
   End With
End Sub

[xcelnovice101]

Thanks a lot AB33 for your help thus far. I think we're close however if "CHEMICALS" can't be found I have other problems then. The key words are exactly the same for each tab and each tab has all of them in the same columns. The debugging message I'm receiving is: "Run-time error '91': Object variable or With block variable not set" What I've noticed is the .Range lines of code will work on whatever Sheet I have currently selected and NOT loop to the next Sheet unless I manually select it. Therefor it's trying to repeat the same code over the same ranges. If I hover over the highlighted section of code below, VBA shows me the correct range of (in this case) L11:L38.

.Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"

I'm not sure why the code works the first time on a set of ranges and then bugs out on the second loop over the same set of ranges, regardless of the fact that it isn't looping to the "Next ws", but that's question I'm not sure I need answered at this time.

[AB33]

Could you please attach the sample book?

[xcelnovice101]

I noticed the code will only run on the first tab but will reference the ranges for whatever Sheet is the active sheet.

[AB33]

Okay!
A couple of points on your code.
It is a good practice to use Option Explicit in your code(At the top). Since mine is default set, I get an error( Not defined variable). You have lots of undefined variables.
The code loops through all sheets, including hidden sheets. You have 3 sheets in the collection, but 2 are shown. Since the code loops through all sheets, it errors on the hidden sheet.

[AB33]

'Option Explicit
Sub loopthrough()


'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            If .Visible = xlSheetVisible Then
            
'CREATE HEADER
            .[J6] = "Price"
            .[K6] = "% Change"
            .[L7] = "Company"
            .[M7] = "Industry"
            .[N7] = "Sector"
            .Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
'On Error Resume Next
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(.Range("K" & CHEMICALS), .Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = _
                  "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
            End If
        End With
    Next ws
End Sub

[xcelnovice101]

ABB33, thank you for the advice. I'm not sure how to define "CHEMICALS" and "COAL" in this very short sample which I need to do if I'm going to use Option Explicit. I also noticed VBA was counting 3 sheets but I don't know where the third sheet is; if you try to unhide it, there's nothing to unhide so I don't know how VBA comes up with 3 sheets. Finally, I still don't know why VBA wont move to the "Next ws". The commands under 'CREATE HEADER are very basic and should be executed on the "Next ws" after the first loop but that isn't happening which leads me to beleive that's were the real breakdown is occuring; VBA isn't actually moving to the next sheet.

[Norie]

CHEMICALS and COAL should be declared as Long, or Variant.

PS If you are unclear of which data type to use in a declaration then use Variant, but remember to change that to a specific data type when you work out which one to use.

PPS Instead of using the [] notation try using Range.

[xcelnovice101]

Thanks Norie!

This is now my code and it still debugs at the highlighted code on the second pass through. "Object variable or With block variable not set"

Option Explicit
Sub CalculatePerformance()
Application.ScreenUpdating = False

'REFERENCE VARIABLES
    Dim CHEMICALS As Long, COAL As Long

'SHEET SPECIFIC
    Sheets("Weights").[J7].Formula = "=BDP(""RIY INDEX"",""LAST_TRADE"")"
    Sheets("Weights").[K7].Formula = "=BDP(""RIY INDEX"",""RT_PX_CHG_PCT_1D"")"
    Sheets("Weights_R800").[J7].Formula = "=BDP(""RMC INDEX"",""LAST_TRADE"")"
    Sheets("Weights_R800").[K7].Formula = "=BDP(""RMC INDEX"",""RT_PX_CHG_PCT_1D"")"
    
'LOOP
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
'CREATE HEADER
            .Range("J6") = "Price"
            .Range("K6") = "% Change"
            .Range("L7") = "Company"
            .Range("M7") = "Industry"
            .Range("N7") = "Sector"
            .Columns("A:A").Replace What:=".", Replacement:="/", LookAt:=xlPart
            
'BASIC MATERIALS
'CHEMICALS
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(.Range("K" & CHEMICALS), .Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
        End With
    Next ws
Application.ScreenUpdating = True
End Sub

[Norie]

That means that Find isn't finding 'CHEMICALS' in column C of the current worksheet you are looping through.

Now that doesn't actually mean it's not there, the problem could be with Find.

You could try using Match to find the row 'CHEMICALS' (or 'Chemicals') is in and then use that in the code to place the formula.

Dim CHEMICALS As Variant
Dim COAL As Variant
Dim rw As Variant

    ' other code
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range("J" & CHEMICALS & ":J" & COAL).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range("K" & CHEMICALS & ":K" & COAL).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range("L" & CHEMICALS & ":L" & COAL).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            
            rw = Application.Match("CHEMICALS", .Columns(3), 0)

            If Not IsError(rw) Then
                .Cells(rw, 13).Formula = "=SUM(L" & CHEMICALS & ":L" & COAL & ")"
            End If

[AB33]

The code loops through the sheets, except the hidden sheet (Please check my last post). If the loop skips that(Which is hidden) sheet, the code does not error and you should be able to see data in to visible sheets though, I do not know what the code does.
As I said on previous post, the code will error if it could not find the searched item, hence you need to built error trap on the find function.

[xcelnovice101]

Okay, so I took the code I had and added it to a new worksheet with just 2 tabs (which is what I thought I had to begin with) and now the code will loop fully twice, which is for the number of sheets I have in this file. It works great but for one exception. My ranges which are defined by the location of "CHEMICALS" and "COAL" are the ranges for whichever Sheet I have as the active sheet when I run the macro; so the range is not corrrect for one of the sheets buts works perfectly for the other. Is there a better alternative for what I'm trying to accomplish than the following?

'BASIC MATERIALS
'CHEMICALS
            Dim CHEMICALS As Variant, COAL As Variant
            CHEMICALS = Evaluate("=MATCH(""CHEMICALS"",$C:$C,0)+1")
            COAL = Evaluate("=MATCH(""COAL"",$C:$C,0)-1")
            .Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).Formula = "=BDP(RC[-9]&"" EQUITY"",""LAST TRADE"")"
            .Range(.Range("K" & CHEMICALS), .Range("K" & COAL)).Formula = "=BDP(RC[-10]&"" EQUITY"",""RT_PX_CHG_PCT_1D"")"
            .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Formula = "=(RC[-1]-R7C11)*RC[-5]"
            .Columns("C:C").Find(What:="CHEMICALS").Offset(0, 10).Formula = "=SUM(" & .Range(.Range("L" & CHEMICALS), .Range("L" & COAL)).Address(False, False) & ")"
'COAL

[Norie]

You need to specify the sheet name in the formula(s) you are evaluating, or not use Evalute.

CHEMICALS = Application.Match("CHEMICALS",ws.Columns(3), 0) +1)
COAL = Application.Match("COAL",ws.Columns(3), 0) +1)

By the way, you can write the ranges for the formula like this,

.Range("J" & CHEMICALS & ":J" & COAL).Formula = ...

no need for this.

.Range(.Range("J" & CHEMICALS), .Range("J" & COAL)).

[xcelnovice101]

I'm getting an "Application-defined or object-defined error" with the following line of code.

Dim CHEMICALS As Variant, COAL As Variant
CHEMICALS = Application.Match("CHEMICALS", ws.Columns(3, 0) + 1)

[Norie]

That's not the code I posted, there's ) in the wrong place.

And there's an extra ) in what I posted.

CHEMICALS = Application.Match("CHEMICALS", ws.Columns(3), 0) + 1
COAL = Application.Match("COAL", ws.Columns(3), 0) + 1

[xcelnovice101]

AWESOME! Thank you so much Norie and AB33 for all of your help on this one! It's working like a charm.