+ Reply to Thread
Results 1 to 3 of 3

Insert Column w/ Equation - Fill to bottom

  1. #1
    Registered User
    Join Date
    07-28-2005
    Posts
    37

    Insert Column w/ Equation - Fill to bottom

    I have a series of excel spreadsheets that come in the same format, but there are varying numbers of rows. I have to add four columns with equations in each to be run on the existing data. I know how to add the columns. I cannot get the equation to fill to the bottom of where the existing data is. For example, existing data covers range A2:A24 in Spreadsheet 1 and A2:A29 in Spreadsheet 2. I have code that inserts a column with an equation next to A in B2 on spreadsheet 1, but I cant write the code that will fill that equation to B24 on SS 1 and to A29 in SS 2. I know this is confusing, but hopefully someone can help.

  2. #2
    FSt1
    Guest

    RE: Insert Column w/ Equation - Fill to bottom

    hi,
    insert this code after you add columns and equation.
    Range("b2").Copy
    Range(Range("b2"), Range("b2").Offset(0, -1) _
    .End(xlDown).Offset(0, 1)).PasteSpecial xlPasteAll

    regards
    FSt1

    "mkerstei" wrote:

    >
    > I have a series of excel spreadsheets that come in the same format, but
    > there are varying numbers of rows. I have to add four columns with
    > equations in each to be run on the existing data. I know how to add
    > the columns. I cannot get the equation to fill to the bottom of where
    > the existing data is. For example, existing data covers range A2:A24
    > in Spreadsheet 1 and A2:A29 in Spreadsheet 2. I have code that inserts
    > a column with an equation next to A in B2 on spreadsheet 1, but I cant
    > write the code that will fill that equation to B24 on SS 1 and to A29
    > in SS 2. I know this is confusing, but hopefully someone can help.
    >
    >
    > --
    > mkerstei
    > ------------------------------------------------------------------------
    > mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
    > View this thread: http://www.excelforum.com/showthread...hreadid=548718
    >
    >


  3. #3
    GS
    Guest

    RE: Insert Column w/ Equation - Fill to bottom

    Hi mkerstei,

    You could try one of these procedures, depending on what you're doing. They
    are self-explanatory, but modify them as required with your columns and
    formulas.


    Sub InsertFormulas1()
    ' Populates cells adjacent to 4 columns (A,C,E,G) of data with the same
    formula
    Dim c As Variant
    Const sCols As String = "B,D,F,H"

    For Each c In Split(sCols, ",")
    Range(Cells(2, c).Offset(, -1), Cells(2, c).Offset(,
    -1).End(xlDown)).Offset(, 1).Formula = "=row()*2"
    Next
    End Sub

    Sub InsertFormulas2()
    ' Populates cells adjacent to 4 columns (A,C,E,G) of data with different
    formulas
    ' Column positions and formulas are put into string variables
    ' Loads ad trims the variables into a 2D array

    Dim va(1 To 4, 1 To 4) As String
    Dim sCols As String, sFormulas As String, c As String
    Dim i As Integer

    sCols = "B,D,F,H,"
    sFormulas = "=row()*1,=row()*2,=row()*3,=row()*4,"
    'The trailing comma prevents an error on the last InStr() when trimming
    the strings

    'Load the array
    For i = LBound(va) To UBound(va)
    va(i, 1) = Left$(sCols, InStr(1, sCols, ",") - 1)
    sCols = Right$(sCols, Len(sCols) - InStr(1, sCols, ","))
    va(i, 2) = Left$(sFormulas, InStr(1, sFormulas, ",") - 1)
    sFormulas = Right$(sFormulas, Len(sFormulas) - InStr(1, sFormulas, ","))
    Next

    'Populate the cells
    For i = LBound(va) To UBound(va)
    c = va(i, 1)
    Range(Cells(2, c).Offset(, -1), Cells(2, c).Offset(,
    -1).End(xlDown)).Offset(, 1).Formula = va(i, 2)
    Next
    End Sub
    ---

    HTH
    Regards,
    Garry

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1