Copying a formula from Spreadsheet to VBA

Gopee · Nov 27th 2004

Hi, I've entered the following formula into cell A2 ($D$17*(1-D21)). It copies down to the cell I want it to if I do it manually. I want it to copy down the number of times using a macro automatically. The number of times it should copy to is represted in cell I2. How should I go about doing this?

Thanks much appreciated.

Anonymous · Nov 27th 2004

Re: Copying a formula from Spreadsheet to VBA

Something like this should work

Code

Option Explicit


Sub CopyFormula()
Dim i As Long, C As Long
C = Range("I2").Value
For i = 2 To C + 1
Cells(i, 1) = "=($D$17*(1-D" & 19 + i & "))"
Next i
End Sub

lasw10 · Nov 27th 2004

Re: Copying a formula from Spreadsheet to VBA

Something like this?

Code

Sub PASTE_FORMULA()


Dim formrw, cnt, frw, lrw As Integer
formrw = 2 'row containing formula
cnt = Cells(formrw, 9) 'cell containing number of rows to paste formula
If cnt > 0 Then
    frw = formrw + 1 'set first row of range requiring formula
    lrw = formrw + cnt 'set last row of range requiring formula
Else
    'if count = 0 (I2) then exit routine
    Exit Sub
End If


'copy formula
Cells(formrw, 1).Copy
'paste formula to predetermined range
Range(Cells(frw, 1), Cells(lrw, 1)).PasteSpecial xlPasteFormulas


End Sub

Display More

**Jack in the UK** · Nov 27th 2004

Re: Copying a formula from Spreadsheet to VBA

Hi buddy this might get you started, need a lot of error checks or might fails ie if i2 is empty or not whole number, give it a try.

Jack

Code

Option Explicit


Sub J_R_Excel_Solutions_FillA()


' Writte by: Jack in the UK
' [url]www.excel-it.com[/url]
' Excel 2003


Dim i As Long


i = Range("i2").Value


Range("a2").Select
Selection.Copy


' jiuk - must make sure i2 is a value and <> ""
Selection.AutoFill _
     Destination:=Range("a2:a" & i + 2), _
     Type:=xlFillCopy
     
Application.CutCopyMode = False


' juik - return to a cell - edit as needs be
Range("a1").Select


theEnd:
' jiuk -- some code
End Sub

Display More

Copying a formula from Spreadsheet to VBA

Participate now!

Share