Insert column with equation between existing columns

Luin29

New Member
Joined
Oct 7, 2015
Messages
45
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:

Well IDDate SampledPCETCE
VW-11/25/2008ND<5.0ND<5.0
VW-16/20/2008ND<5.0ND<5.0
VW-19/23/2008ND<0.50ND<0.50
VW-111/5/2008ND<0.501.9
VW-13/16/2009ND<0.501.6
VW-16/2/2009ND<0.501.4
VW-19/17/2010ND<0.50ND<0.50

<tbody>
</tbody>

And the final product would look like this:

Well IDDate SampledPCETCE
VW-11/25/2008ND<5.0=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))ND<5.0=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-16/20/2008ND<5.0=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))ND<5.0=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-19/23/2008ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-111/5/2008ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))1.9=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-13/16/2009ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))1.6=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-16/2/2009ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))1.4=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))
VW-19/17/2010ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))ND<0.50=IF(ISBLANK([RC]-1),"",IF(ISTEXT([RC]-1),0,1))

<tbody>
</tbody>

So far I have this:
Code:
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.
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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?
 
Upvote 0
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.
 
Upvote 0
This will insert 2 new columns
Code:
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
 
Upvote 0
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)?
 
Upvote 0
Thank you for your help Fluff. With a bit more research I was about to figure out the code

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
 
Last edited:
Upvote 0
Glad you got it sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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