Insert column with equation between existing columns

[Luin29]

Hello everyone,

I was hoping to have some help changing the following code.

I would like the end product to insert a column between a dynamic range of existing columns with the following equation in each cell within the new columns.

The data table start off looking like this:

[TABLE="width: 298"]
<tbody>[TR]
[TD]Well ID[/TD]
[TD]Date Sampled[/TD]
[TD]PCE[/TD]
[TD]TCE[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]1/25/2008[/TD]
[TD]ND<5.0[/TD]
[TD]ND<5.0[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]6/20/2008[/TD]
[TD]ND<5.0[/TD]
[TD]ND<5.0[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]9/23/2008[/TD]
[TD]ND<0.50[/TD]
[TD]ND<0.50[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]11/5/2008[/TD]
[TD]ND<0.50[/TD]
[TD]1.9[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]3/16/2009[/TD]
[TD]ND<0.50[/TD]
[TD]1.6[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]6/2/2009[/TD]
[TD]ND<0.50[/TD]
[TD]1.4[/TD]
[/TR]
[TR]
[TD]VW-1[/TD]
[TD]9/17/2010[/TD]
[TD]ND<0.50[/TD]
[TD]ND<0.50[/TD]
[/TR]
</tbody>[/TABLE]

And the final product would look like this:

[TABLE="width: 848"]
<tbody>[TR]
[TD="align: center"]Well ID[/TD]
[TD="align: center"]Date Sampled[/TD]
[TD="align: center"]PCE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TCE[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]1/25/2008[/TD]
[TD="align: center"]ND<5.0[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]ND<5.0[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]6/20/2008[/TD]
[TD="align: center"]ND<5.0[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]ND<5.0[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]9/23/2008[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]11/5/2008[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]1.9[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]3/16/2009[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]1.6[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]6/2/2009[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
[TR]
[TD="align: center"]VW-1[/TD]
[TD="align: center"]9/17/2010[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[TD="align: center"]ND<0.50[/TD]
[TD="align: center"]=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))[/TD]
[/TR]
</tbody>[/TABLE]

So far I have this:

Dim i, itotalrows As Integer
Dim strRange As String
itotalrows = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
Do While i <= itotalrows
    i = i + 1
    strRange = "A" & i
    strRange2 = "A" & i + 1
    If Range(strRange).Text <> Range(strRange2).Text Then
        Rows(i + 1).Insert
        Rows(i + 1).Formula = "IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),1,0))"
        itotalrows = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
        i = i + 1
    End If
Loop

Thank you in advance for any help you can provide.

 

[Fluff]

You say that you want to insert columns, but your code is inserting rows.
If it is columns you want will it always columns 4 & 6?

 

[Luin29]

Sorry, I should of specified that the code I have is one that I use for inserting rows and I thought it could be altered to insert columns with equation in the cells. But there is no need if you have another code in mind.

The number of columns will vary but will always start with column 4.

 

[Fluff]

This will insert 2 new columns

Sub Fluff()

    Dim UsdRws As Long
    
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    Columns(4).Insert
    Range("D2:D" & UsdRws).Formula = "=IF(ISBLANK(RC[-1]),"""",IF(ISTEXT(RC[-1]),1,0))"
    Columns(6).Insert
    Range("F2:F" & UsdRws).Formula = "=IF(ISBLANK(RC[-1]),"""",IF(ISTEXT(RC[-1]),1,0))"

        
End Sub

 

[Luin29]

Thank you Fluff for the code but running this code would require me to define each column I would like to enter. Is it possible for the code to be more dynamic (i.e. inserting columns between 10 existing columns in one instant and then inserting columns between 5 existing columns in another)?

 

[Luin29]

Thank you for your help Fluff. With a bit more research I was about to figure out the code

Dim i, itotalcolumns As Integer
Dim strRange As String
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
itotalcolumns = ActiveSheet.Range("AB1").End(xlToLeft).Column
i = 2
Do While i <= itotalcolumns
    i = i + 1
    strRange = Cells(1, i)
    strRange2 = Cells(1, i + 1)
    If StrComp(strRange, strRange2) = 1 Or -1 Then
        Columns(i + 1).Insert
        Range(Cells(2, i + 1), Cells(lRow, i + 1)).Formula = "=IF(ISBLANK(RC[-1]),"""",IF(ISTEXT(RC[-1]),1,0))"
        itotalcolumns = ActiveSheet.Range("AB1").End(xlToRight).Column
        i = i + 1
    End If
Loop

 

[Fluff]

Glad you got it sorted & thanks for the feedback