Reputation: 17
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
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