Thanks:  0
Likes:  0

# Thread: Insert column with equation between existing columns

1. ## Insert column with equation between existing columns

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 ID Date Sampled PCE TCE VW-1 1/25/2008 ND<5.0 ND<5.0 VW-1 6/20/2008 ND<5.0 ND<5.0 VW-1 9/23/2008 ND<0.50 ND<0.50 VW-1 11/5/2008 ND<0.50 1.9 VW-1 3/16/2009 ND<0.50 1.6 VW-1 6/2/2009 ND<0.50 1.4 VW-1 9/17/2010 ND<0.50 ND<0.50

And the final product would look like this:

 Well ID Date Sampled PCE TCE VW-1 1/25/2008 ND<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-1 6/20/2008 ND<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-1 9/23/2008 ND<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-1 11/5/2008 ND<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-1 3/16/2009 ND<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-1 6/2/2009 ND<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-1 9/17/2010 ND<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))

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```

2. ## Re: Insert column with equation between existing columns

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?

3. ## Re: Insert column with equation between existing columns

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.

4. ## Re: Insert column with equation between existing columns

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```

5. ## Re: Insert column with equation between existing columns

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)?

6. ## Re: Insert column with equation between existing columns

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```

7. ## Re: Insert column with equation between existing columns

Glad you got it sorted & thanks for the feedback

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•