I can't insert a column in a loop with VBA

[mvaldez]

Hello dear experts.

I would greatly appreciate your help with this problem that I have to insert a column to the left in the row if below the value in Range(x) of the cell is not equal to one. I have tried many ways, but the routine inserts many columns to the right.

Could you help me with this case, please?

In advance, I am very grateful for your valuable help.

The code I wrote is the following:

Sub InsertColumns()

Dim myCell As Range
Dim MyRange As Range

Set MyRange = ActiveSheet.Rows(5).SpecialCells(xlCellTypeConstants, xlNumbers)
For Each myCell In MyRange
x = myCell.Address(False, False)
MsgBox "x: " & x

Range(x).Select

For i = 1 To 1 Step -1
With Range(x)
.EntireColumn.Insert
.Offset(0, -1).ColumnWidth = 5
End With
Next

Next

Range("A1").Select

End Sub

Inserting Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
5			2008	2013						2014					2016										2017									2018
6	Departamento	Municipio	21	11	12	13	14	15	16	3	5	6	7	8	1	2	3	4	5	6	7	8	9	14	1	2	3	4	5	6	7	8	9	1	2	3	4	5	6	7	8	9	10	11	12	13	14
7	Baja Verapaz	Cubulco																							0	0	0	0	0	0	0	0	0
8	Baja Verapaz	El Chol																							0	0	0	0	0	0	0	0	0
9	Baja Verapaz	Granados																							0	0	0	0	0	0	0	0	0
10	Baja Verapaz	Purulha																							0	0	0	0	0	0	0	0	0
11	Baja Verapaz	Rabinal																							0	0	0	0	0	0	0	0	0
12	Baja Verapaz	Salama																							0	0	0	0	0	0	0	0	0
13	Baja Verapaz	San Jeronimo																							0	0	0	0	0	0	0	0	0
14	Baja Verapaz	San Miguel Chicaj																							0	0	0	0	0	0	0	0	0
Cosecha 3

Thank you very mcuh in advance.

 

[rlv01]

Perhaps something like this.

Sub InsertColumns()
    
    Dim myCell As Range
    Dim MyRange As Range
    Dim S As String, V
    
    Set MyRange = ActiveSheet.Rows(5).SpecialCells(xlCellTypeConstants, xlNumbers)
    For Each myCell In MyRange
        V = myCell.Offset(1).Value
        
        S = "     x: " & myCell.Address(False, False) & " = " & myCell.Value & vbCr
        S = S & "x+1: " & myCell.Offset(1).Address(False, False) & " = " & V
        MsgBox S
        
        If V = 1 Then
            myCell.EntireColumn.Insert shift:=xlLeft
        End If
    Next myCell
End Sub

 

[mvaldez]

Hello rlv01.

I greatly appreciate your soon answer!

The macro works fine if the value is equal to one, but my problem is that I want to insert a column to the left if the value below the year is NOT equal to one.

When the value is not equal to one, the code inserts many columns to the right.

How could you solve this problem?

 

[Joe4]

I cannot speak for anyone else, but it is not 100% clear to me exactly what you are trying to do and where.
You posted a picture in your original post. Can you walk us through an example of exactly what should happen and where, based on that image?
Or maybe show us another image, of what your example should look like AFTER the macro is done.

 

[rlv01]

The macro works fine if the value is equal to one, but my problem is that I want to insert a column to the left if the value below the year is NOT equal to one.

Still not clear. What if you changed

      If V = 1 Then

to

      If V <> 1 Then

Does that do what you want?

BTW, it would help a lot if you would use code tags to post your code:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[mvaldez]

This is how it should look. If the number one does NOT exist under the year, you must insert a column from the left side in inserting the number one in the new column.

Thank you.

Inserting Columns.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
5				2008		2013							2014					2016										2017
6	Departamento	Municipio	1	21	1	11	12	13	14	15	16	1	3	5	6	7	8	1	2	3	4	5	6	7	8	9	14	1	2	3	4	5	6	7	8	9
7	Baja Verapaz	Cubulco																										0	0	0	0	0	0	0	0	0
8	Baja Verapaz	El Chol																										0	0	0	0	0	0	0	0	0
9	Baja Verapaz	Granados																										0	0	0	0	0	0	0	0	0
10	Baja Verapaz	Purulha																										0	0	0	0	0	0	0	0	0
11	Baja Verapaz	Rabinal																										0	0	0	0	0	0	0	0	0
12	Baja Verapaz	Salama																										0	0	0	0	0	0	0	0	0
13	Baja Verapaz	San Jeronimo																										0	0	0	0	0	0	0	0	0
14	Baja Verapaz	San Miguel Chicaj																										0	0	0	0	0	0	0	0	0
Cosecha 3

 

[Joe4]

OK, I think I see now.

Try this:

Sub MyInsertColumns()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = Cells(5, Columns.Count).End(xlToLeft).Column To 3 Step -1
        If Cells(5, c) > 0 And Cells(6, c) <> 1 Then
            Columns(c).Insert
            Cells(6, c) = 1
        End If
    Next c
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete"
    
End Sub

 

[mvaldez]

Oh my God!

This is wonderful!

You the experts are real genius!

My respects to you!

Finally, there's a solution after trying several weeks and breaking my head!

Thank you very much for your valuable help!

 

[mvaldez]

Thank you very much rlv01 and Joe4 for your help!

Have a wonderful day!

 

[Joe4]

You are most welcome!
Glad we were able to help.