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?
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:
Thank you in advance for any help you can provide.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
Last edited by Luin29; Aug 12th, 2017 at 04:56 PM.
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?
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Running Office 2003 & 2013 on Win 7
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.
This will insert 2 new columnsCode: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
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Running Office 2003 & 2013 on Win 7
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)?
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 by Luin29; Aug 12th, 2017 at 08:12 PM.
Glad you got it sorted & thanks for the feedback
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Running Office 2003 & 2013 on Win 7
Like this thread? Share it with others