Run Time Error 1004 - Excel Macro - Code was running fine earlier

Question

I have the following code to insert a new row in a table after the last row having values.

Private Sub CommandButton1_Click()
    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer, additionalRow As Integer, additionalRowCounter As Integer, offsetRowCounter As Integer
    Dim currentRowValue As String
    'Dim Step5 As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("2.Set Up")

    ws.Activate
    sourceCol = 3   'Entity Name is column C, which has a value of 6
    additionalRow = 0
    offsetRowCounter = 0
    rowCount = ws.Cells(Rows.Count, sourceCol).End(xlUp).Row

    'for every row starting from 4th row, find the first blank cell and select it
    For currentRow = 4 To rowCount
        currentRowValue = ws.Range(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, sourceCol).Select
            Rows(currentRow).Insert shift:=xlShiftDown
            Exit For
        End If
        additionalRow = additionalRow + 1
   Next
End Sub

The tables starts from C4.

Earlier it was working fine, but from yesterday I'm getting

Run-Time error 1004 while compiling the code.

I haven't made any change in the sheet.

Kindly suggest what can be the issue

In which line do you get the error and what exactly is the error message? — Pᴇʜ, Aug 15 '18 at 07:21
Note that you must use `Long` for all variables that handle row counts, because Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) because there is no benefit in using `Integer` in VBA. — Pᴇʜ, Aug 15 '18 at 07:23
Also as a sidenote, if your goal is to add a row after the last used row then why are you looping through all rows? + Try to refrain from using `.select` :) — JvdV, Aug 15 '18 at 07:27
The error is in the following code: "currentRowValue = ws.Range(currentRow, sourceCol).Value". I also changed the integer to Long, but still getting the same error — Rishi, Aug 15 '18 at 07:31
What are the values of currentRow, sourceCol when the error occurs? Also, wrap your operations inside a With ws if you intend to work with that sheet otherwise it will use the Activesheet for Cells(currentRow, sourceCol).Select Rows(currentRow).Insert shift:=xlShiftDown — QHarr, Aug 15 '18 at 07:33
I commented the code which was using .select, but still getting the same error — Rishi, Aug 15 '18 at 07:33
Sorry I am a bit of beginner in Macro. How can i see the values of currentRow? sourceCol should always be 3 i believe, since I am not changing it at all. — Rishi, Aug 15 '18 at 07:35
Step through your code using `F8`, then right after your error, hover over the variables to see their value! — JvdV, Aug 15 '18 at 07:36
CurrentRow is 4, rowcount is 85, currentRowValue is "". Also, it is saying "" when I hover over the error line — Rishi, Aug 15 '18 at 07:40
If you are doing all that just to get the total rows in your table and then add a row to it, you could get the total rows just by trying something like this: `ThisWorkbook.Sheets("2.Set Up").ListObjects("MyTable").Range.Rows.Count` — Zac, Aug 15 '18 at 07:46

JvdV · Accepted Answer · 2018-08-15T07:51:41.550

1

You are using Index numbers to refer to a cell but on the same time you want to get the value using the Range notation. So change this line:

currentRowValue = ws.Range(currentRow, sourceCol).Value

to:

currentRowValue = ws.cells(currentRow, sourceCol).Value

Want to know more?

edited Aug 15 '18 at 07:51

answered Aug 15 '18 at 07:44

JvdV

70,606
8
39
70

Run Time Error 1004 - Excel Macro - Code was running fine earlier

1 Answers1