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

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

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