31256

Getting runtime error 1004 How to stop error

Question:

I have the following code which I keep getting runtime error 1004. How or what can you suggest to stop error at the ActiveCell.Offset(1, 0).Select in the below code:

Sub RowCounter (count) Counter = 1 Do Until ActiveCell = "" Counter = counter + 1 ActiveCell.Offset(1, 0).Select Loop Count = Counter > 2000000

Answer1:

The code counts how many cells with value are below the activecell. Try this to avoid the error, showing the answer in a MsgBox():

Sub RowCounter() Dim counter As Long counter = 1 Do Until ActiveCell = "" Or ActiveCell.Row <> Rows counter = counter + 1 ActiveCell.Offset(1, 0).Select Loop MsgBox counter End Sub

The Or ActiveCell.Row <> Rows makes sure to exit, if the ActiveCell is on the last row of the Excel spreadsheet.

Concerning the .Select and ActiveCell part, see this topic with some ideas how to improve it:

<ul><li><a href="https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba" rel="nofollow">How to avoid using Select in Excel VBA</a></li> </ul>

Recommend

  • VBA - syntax for .pictures.insert incorrect? Insert method of picture class failed
  • Excel - Macro to create chart based on selection
  • Creating an array variable with a variable number of elements
  • Get highest value from a file using mSL and mIRC
  • How to override JAXB @XMLAccessorType(XMLAccessType.FIELD) specified at a Class level with @XMLEleme
  • google-apps-script count files in folder
  • -[__NSArrayM insertObject:atIndex:]: object cannot be nil
  • Facebook Open Graph Story Custom Actions Keep Getting Rejected - Advice Please?
  • Is there any way to center certain columns in table?
  • finding symmetric difference/unique elements in multiple arrays in javascript
  • Make background for table rows extend past the bounds of the table
  • What distributed message queues support millions of queues?
  • How to delete column from range if cell contains specific value in VBA/Excel
  • How to delete a newline using \\b
  • Is it possible to “shrink” a PdfPtable?
  • Excel passing a range into a function
  • Convert C# statements including NSubstitute code to VB.net
  • R h2o.glm - issue with max_active_predictors
  • get all processes in parallel
  • xtable - background colour of added rows
  • cell spacing in div table
  • Using Sax parsing to edit and write XML in VB6
  • How VBA declared Volatility works
  • how to avoid repetitive constructor in children
  • Excel's Macro-Recorder usage
  • How to get Eclipse Oxygen to run on Java 9
  • Django: Count of Group Elements
  • Reading JSON from a file using C++ REST SDK (Casablanca)
  • Control modification in presentation layer
  • Excel - Autoshape get it's name from cell (value)
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • vba code to select only visible cells in specific column except heading
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize