Using Excel formulas with and without RC notation in VBA,VBA Teacher Sourav,Kolkata 08910141720

 Without RC notation

Sub usingexcelfunctioninvba()

Sheets("prg").Select
Dim workingrange As Range


Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Set workingrange = Selection
Dim sumresult As Integer
sumresult = Application.sum(workingrange)
Dim average As Double
average = Application.average(workingrange)
Dim count As Integer
count = Application.count(workingrange)
Dim max, min As Double
max = Application.max(workingrange)
min = Application.min(workingrange)
Dim stdevsample As Double
stdevsample = Application.WorksheetFunction.StDev_S(workingrange)
Dim stdevpopulation As Double
stdevpopulation = Application.WorksheetFunction.StDev_P(workingrange)
Dim median, mean, mode As Double
median = Application.WorksheetFunction.median(workingrange)
'mean = Application.WorksheetFunction.mean(workingrange)
mode = Application.mode(workingrange)
Range("A5").Value = sumresult
Range("B5").Value = average
Range("C5").Value = count
Range("D5").Value = max
Range("E5").Value = min
Range("F5").Value = stdevsample
Range("G5").Value = stdevpopulation
Range("H5").Value = average
Range("I5").Value = median
Range("J5").Value = mode

End Sub


With RC notation

Option Explicit

Sub usingrcformulainvba()
Sheets("prg").Select
Range("A1").Select
Dim firstrangerow As Integer
firstrangerow = (ActiveCell.Row)
Dim firstrangecolumn As Integer
firstrangecolumn = (ActiveCell.Column)
Selection.End(xlToRight).Select
Dim lastrangerow As Integer
lastrangerow = (ActiveCell.Row)
Dim lastrangecolumn As Integer
lastrangecolumn = (ActiveCell.Column)

Range("A7").Select

Dim currentrangerow As Integer
currentrangerow = (ActiveCell.Row)
Dim currentrangecolumn As Integer
currentrangecolumn = (ActiveCell.Column)

'MsgBox (firstrangerow - currentrangerow)


ActiveCell.FormulaR1C1 = "=SUM(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"
ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=Average(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=count(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=max(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=min(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=stdev.s(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=stdev.p(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=average(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=median(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"
End Sub