Insert column with equation between existing columns

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Insert column with equation between existing columns

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Thank you in advance for any help you can provide.
    Last edited by Luin29; Aug 12th, 2017 at 04:56 PM.

  2. #2
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,142
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

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

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,142
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

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

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Oct 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Luin29; Aug 12th, 2017 at 08:12 PM.

  7. #7
    Board Regular Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    3,142
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Insert column with equation between existing columns

      
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com