CODEWITHSUNDEEP
vbaexcel
Rishi
Rishi

Reputation: 17

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

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

Upvotes: 0

Views: 90

Answers (1)

JvdV
JvdV

Reputation: 75980

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?

Upvotes: 1

Related Questions