Run-time error '1004': Insert method of Range class failed when running VBA to insert cells

Dan Pilgrim

New Member
Joined
Dec 4, 2014
Messages
5
ABCDEFGHIJKL
1
2661DeleteItem809DanPilgrim3280
3721DeleteMatrix809DanPilgrim3280
4727AmendMatrix-CrIntDet809DanPilgrim3280
5
6

<tbody>
</tbody>

Hi all,

I've been considering this for days and have now given up. I have a spreadsheet much like the one above and I'm trying to develop a macro to insert 4 cells from column D based on the value of column A.

I've come up with the following so far, and know it doesn't do it from D, but get a Run-time error '1004'. Please help.

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value = "661" Or "721" Then .Insert Shift:=x4ToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Eventually I need to be able to shift cells D2:H2 & D3:H3 to H2:L2 & H3:L3 respectively because the values in column A for those 2 rows satisfy the argument if that makes any sense.
 
Upvote 0
something like

Dim c As Range, rng
Set rng = Range("a1:a3")
For Each c In rng
If c.Value = 661 Or c.Value = 721 Then
c.Offset(0, 3).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 4).Select
Selection.Cut
c.Offset(0, 7).Select
ActiveSheet.Paste
End If
Next c
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top