Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Column w/ Equation - Fill to bottom
I have a series of excel spreadsheets that come in the same format, bu there are varying numbers of rows. I have to add four columns wit equations in each to be run on the existing data. I know how to ad the columns. I cannot get the equation to fill to the bottom of wher the existing data is. For example, existing data covers range A2:A2 in Spreadsheet 1 and A2:A29 in Spreadsheet 2. I have code that insert a column with an equation next to A in B2 on spreadsheet 1, but I can write the code that will fill that equation to B24 on SS 1 and to A2 in SS 2. I know this is confusing, but hopefully someone can help -- mkerste ----------------------------------------------------------------------- mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568 View this thread: http://www.excelforum.com/showthread.php?threadid=54871 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy last entry of column to bottom of column | Excel Worksheet Functions | |||
show last used cell in a column at the bottom of column. | Excel Worksheet Functions | |||
Insert MathType Equation | Excel Discussion (Misc queries) | |||
dislike jump bottom of column by double-clicking the bottom of cel | Excel Discussion (Misc queries) | |||
Insert Text at Bottom of Last Page | Excel Programming |