44720

Looping through all worksheets VBA

Question:

I am trying to loop through all the worksheets in the activeworkbook to perform a repetitive task.

I currently have the code below:

Sub sort_sectors() Dim i As Integer Dim rng As Range Dim SortRng As Range Dim rng1 As Integer Dim ws As Worksheet Dim wb As Workbook Dim LastCol As Long Dim LastRow As Long Set wb = ActiveWorkbook For Each ws In wb.Worksheets 'This is marking several of the sheets of which I do not want to run the sub If ws.Range("a9").Value = "x" Then NextIteration: End If 'Reference point is rng1 to select the desired range With Range("a1:t100") rng1 = .Find(what:="sector", LookIn:=xlValues).Row End With 'return the row number for the sector header LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column LastRow = ws.Range("a15").End(xlDown).Row 'I am going to add the code below to finish out the task that I want to complete Next End Sub

I am sure the problem is that I'm misunderstanding something about how the for each loop actually works. Hopefully someone's answer will allow to better understand.

I really appreciate any help on this.

I made some edits to the code, and now I actually do have an error :) I tried making the changes you suggested for the "with ws.range etc..." piece of the code, and I get the object error 91.

Below is my new and "improved" code.

Sub sort_sectors() Dim i As Integer Dim rng As Range Dim SortRng As Range Dim intAnchorRow As Integer Dim intMktCapAnchor As Integer Dim intSectorAnchor As Integer Dim ws As Worksheet Dim wb As Workbook Dim LastCol As Long Dim LastRow As Long Set wb = ActiveWorkbook For Each ws In ActiveWorkbook.Worksheets 'Filter out the sheets that we don't want to run If ws.Range("a9").Value <> "x" Or ws.Name = "__FDSCACHE__" Or ws.Name = "INDEX" Then 'Get the anchor points for getting sort range and the sort keys ''''''THIS IS THE PART THAT IS NOW GIVING ME THE ERROR''''''' With ws.Range("a1:t100") intAnchorRow = .Find(what:="sector", LookIn:=xlValues).Row intSectorAnchor = .Find(what:="sector", LookIn:=xlValues).Column intMktCapAnchor = .Find(what:="Market Cap", LookIn:=xlValues).Column End With 'Find the last row and column of the data range LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column LastRow = ws.Range("a15").End(xlDown).Row Set SortRng = Range(Cells(intAnchorRow + 1, 1), Cells(LastRow, LastCol)) Range(SortRng).Sort key1:=Range(Cells(intAnchorRow + 1, intSectorAnchor), Cells(LastRow, intSectorAnchor)), _ order1:=xlAscending, key2:=Range(Cells(intAnchorRow + 1, intMktCapAnchor), Cells(LastRow, intMktCapAnchor)), _ order2:=xlDescending, Header:=xlNo End If Next End Sub

Thanks again. This has been very helpful for me.

Answer1:

If I've understood your issue correctly, you don't want to use a worksheet with an x in cell A9.

If that's the case I would change the condition of the if statement to check if the cell does not contain the x. If this is true, it enters the rest of the code. If not, it goes to the next iteration.

Also, your NextIteration: doesn't do anything in the If statement.

Sub sort_sectors() Dim i As Integer Dim rng As Range Dim SortRng As Range Dim rng1 As Integer Dim ws As Worksheet Dim wb As Workbook Dim LastCol As Long Dim LastRow As Long Set wb = ActiveWorkbook For Each ws In wb.Worksheets 'This is marking several of the sheets of which I do not want to run the sub If ws.Range("a9").Value <> "x" Then 'Reference point is rng1 to select the desired range With Range("a1:t100") rng1 = .Find(what:="sector", LookIn:=xlValues).Row End With 'return the row number for the sector header LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column LastRow = ws.Range("a15").End(xlDown).Row 'I am going to add the code below to finish out the task that I want to complete End If Next End Sub

The : operator is used to return the code to that line after a goto call.

For example

sub gotoEx() for i = 1 to 10 if i = 5 then goto jumpToHere end if next i jumpToHere: '<~~ the code will come here when i = 5 'do some more code end sub

And of course you can use this structure in your code if you wish, and have the jumpToHere: line just before the next

e.g.

for each ws in wb.Worksheets if ws.Range("a9").Value = "x" then goto jumpToHere end if 'the rest of your code goes here jumpToHere: next

Recommend

  • Need assistance with excel macro- vba
  • How to set wallpaper (viewpager)
  • How to generate a Pivot Datagridview in WinForms and EntityFramework
  • Finding and deleting last column with header if sum is 0
  • vba userform , if any of the checkboxes in the frame is true then macro should not be applied on the
  • VBA Multiple loops match conditions
  • cell spacing in div table
  • Primefaces :radioButton inside a ui:repeat
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • Excel's Macro-Recorder usage
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Django: Count of Group Elements
  • Handling un-mapped Rest path
  • FFmpeg Conversion Error
  • When to use `image` and when to use `Matrix` in Emgu CV?
  • Is there a javascript serializer for JSON.Net?
  • Javascript simulate pressing enter in input box
  • Excel - Autoshape get it's name from cell (value)
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Obtain ObjectIdHex value from mgo query
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • ILMerge & Keep Assembly Name
  • Where to put my custom functions in Wordpress?
  • vba code to select only visible cells in specific column except heading
  • Which linear programming package should I use for high numbers of constraints and “warm starts” [clo
  • Why winpcap requires both .lib and .dll to run?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • Acquiring multiple attributes from .xml file in c#
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Can't mass-assign protected attributes when import data from csv file
  • Is there any way to bind data to data.frame by some index?
  • Binding checkboxes to object values in AngularJs
  • How can i traverse a binary tree from right to left in java?
  • Unable to use reactive element in my shiny app