Need help on VBA Macro for Dynamic Formulae

[Manan]

Hi All,

I have requirement where based on inputs cells i am building one formulae and populatig the destination cols, but as number of input formulae can vary so i have written a Dynamic VBA code as below but it is throwing some error. Can someone help me out please. First i am running the VOLACAL() then DYNVOLA() , but i am getting error in DYNVOLA() as AUTOFILL METHOD OF RANGE CLASS FAILED.

Sub VolaCal()
'
' VolaCal Macro
'
'
  Range("F3").Select
  ActiveCell.FormulaR1C1 = "=ABS(RC[-1]-R[-1]C[-1])"
  Range("G3").Select
  ActiveCell.FormulaR1C1 = "=ABS(RC[-2]-RC[-5])"
  Range("H3").Select
  ActiveCell.FormulaR1C1 = _
  "=IF(RC[-6]>RC[-3],ABS(RC[-6]-RC[-4]),ABS(RC[-6]-RC[-5]))"
  Range("H4").Select
  Range("I3").Select
  ActiveCell.FormulaR1C1 = "=POWER(RC[-3]*RC[-2]*RC[-1],1/3)"
  Range("I4").Select
End Sub
Sub DynVola()
'
' DynVola Macro
'
'
  Dim strRng As Long
  strRng = Range("A" & Rows.Count).End(xlUp).Row
  Selection.AutoFill Destination:=Sheets("Sheet2").Range("F3:I" & strRng)
  Sheets("Sheet2").Range("F3:I" & strRng).Select
End Sub

 

[Debraj]

Try something like this...

Sub DynVola()
'
' DynVola Macro
'
'
 Dim strRng As Long
  strRng = Range("A" & Rows.Count).End(xlUp).Row
  Sheets("Sheet2").Range("F3:I3").Select
  Selection.AutoFill Destination:=Sheets("Sheet2").Range("F3:I" & strRng)
  Sheets("Sheet2").Range("F3:I" & strRng).Select
End Sub

PS: As you are is learning phase.. try to avoid.. using Select as much as possible..

 

[Luke M]

Try this.

Sub VolaCal()
'
' VolaCal Macro
'
'VB is an object based languange, which means you can
'apply the formulas directly to obbect/cell, w/o having
'to do all the selecting

Range("F3").FormulaR1C1 = "=ABS(RC[-1]-R[-1]C[-1])"
Range("G3").FormulaR1C1 = "=ABS(RC[-2]-RC[-5])"
Range("H3").FormulaR1C1 = "=IF(RC[-6]>RC[-3],ABS(RC[-6]-RC[-4]),ABS(RC[-6]-RC[-5]))"
Range("I3").FormulaR1C1 = "=POWER(RC[-3]*RC[-2]*RC[-1],1/3)"

End Sub
Sub DynVola()
'
' DynVola Macro
'
'AutoFill gets applied to starting range, so we will specify start point
Dim strRng As Long
strRng = Range("A" & Rows.Count).End(xlUp).Row
Range("F3:I3").AutoFill Destination:=Sheets("Sheet2").Range("F3:I" & strRng)
End Sub