PDA

View Full Version : Solved: Filling a formula



Klartigue
09-08-2011, 08:57 AM
Sub bloomberg()
'
' bloomberg Macro
'
'
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1000")
Range("F2:F1000").Select
End Sub


As you can see, this macro fills the formula from F2 to F1000. However, I want it to fill only how many rows are in column F. There could be 2 rows or 100 rows. How to I create a general macro to fill column F?

Bob Phillips
09-08-2011, 09:11 AM
I'll tell you when you add VBA tags.

Klartigue
09-08-2011, 09:16 AM
Sub bloomberg()
'
' bloomberg Macro
'
'
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1000")
Range("F2:F1000").Select
End Sub

Bob Phillips
09-08-2011, 09:19 AM
Excellent, thank-you.



Sub bloomberg()
'
' bloomberg Macro
'
'
Dim lastrow As Long

lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Resize(lastrow - 1).FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
End Sub

Klartigue
09-08-2011, 09:24 AM
Thank you!