Help with taking formula to vba code

[Sasha]

Hi all

Obviously I am new to VBA code

I am getting this data from VBA code

Total MONTH TYPE D E F G
-===================================================
1 0 Air Cooled 1 0 0 0
0 32 Column 16 11 5 0
2 0 Fire 0 1 0 1
489 0 Pipe 94 123 143 129
169 0 Pressure 16 51 52 50
126 0 Shell 53 33 33 7
17 0 Storage 3 11 2 1
836 0 Total 183 230 235 188

For the next Column I need to apply this Formula
=(10*D8+7*E8+4*F8+1*G8)/(D8+E8+F8+G8)

Now the no: of rows cld change.
I plan to do this, After I print all the data, I will fire a function
that look at D,E,F,G and do the calculations
Can I continue that to the bottom rows?

Thanx for ur help

 

[Guest]

Hi
Try the following function

Sub ApplyFormula()
Dim r&
With Sheet1

r = .Cells(1,1).end(xlDown).Row
With .Range(.Cells(1,8),.Cells(r,8))
'=(10*D8+7*E8+4*F8+1*G8)/(D8+E8+F8+G8)
.FormulaR1C1 =
"=(10*RC[-4]+7*RC[-3]+4*RC[-2]+RC[-1])/(RC[-4]+RC[-3]+RC[-2]+RC[-1])
End with
End with
End sub

Alok

 

[sasha]

ALok

Thanks But,Dnt think I Understand what R1C1 is
Can u write this more specific.

Say I need to start at Row 8 and continue until row 15
Column is 9 (I know the beginning row no and ending row number)

How will u go abt it and then I cld figure out myself what R1C1 is

With Sheet
With .Range(.Cells(8, 9), .Cells(15, 9))
.FormulaR1C1= "(10*D8+7*E8+4*F8+1*G8)/D8+E8+F8+G8)"
End With
End With

I wld need help in the formula area?

Also why isnt this working?
.Range("I8").AutoFill Range("I8:I15")

Thanks

 

[Guest]

Hi Sasha,

Firstly the FormulaR1C1 is a method by which you specify a formula based on
Row and Column offsets or row and column numbers
R8 means row 8. Similarly C3 means the third column or Column C.
R by itself means the current row. R[-1] means previous row. R[2] means two
rows beyond the current row.
Thus if you want to put a summation formula in A8 that sums A1 to A7 you can
do it
as
Sheet1.Cells(8,1).FormulaR1C1 = "=Sum(R1C:R7C)"
or you can do it using the offset notation.
Sheet1.Cells(8,1).FormulaR1C1 = "=Sum(R[-7]C:R[-1]C)"

In your case if you know the start and end of the range in which you want to
put the formula you can just use those row numbers like shown below

With .Range(.Cells(8,8),.Cells(15,8))

..FormulaR1C1 =
"=(10*RC4+7*RC5+4*RC6+RC7)/(RC4+RC5+RC6+RC7)"

End with

With this formula there is no need to autofill as this will automatically
fill the formula from H8 to H15

Alok

 

[sasha]

alok,

thanx a lot, that ws a gr8 explanation. u guys rock

But when I put this formula, not the calculated value is appearing but
this =(10*$D8+7*$E8+4*$F8+$G8)/($D8+$E8+$F8+$G8)

The string itself!!!!

Heres my function

Sub CalculateFormula(ByVal source As Integer, ByVal Dest As Integer)
With wsData
With .Range(.Cells(source, 9),
.Cells(Dest,9)).FormulaR1C1 =
"= (10*RC4+7*RC5+4*RC6+RC7)/(RC4+RC5+RC6+RC7)"
End With
End With
End Sub

 

[Guest]

Hi,
The code to use is as follows

Sub CalculateFormula(ByVal source As Integer, ByVal Dest As Integer)
With DataSh
With .Range(.Cells(source, 9), .Cells(Dest, 9))
.FormulaR1C1 = "=(10*RC4+7*RC5+4*RC6+RC7)/(RC4+RC5+RC6+RC7)"
End With
End With
End Sub

Note that .FormulaR1C1=... line is by itself that is it cannot be a
continuation of the Second With statement.

Alok

Alok