VBA – 12. Using Formulas in VBA

Refer to these screen shots for the code which follows after these images posted below:

(Sheet1 in the Example File)


(Sheet2 in the Example File)FORM3
‘Dim r As Variant

‘Dim rv As String
‘Dim cv As String

‘Dim match_row As Variant
‘Dim match_col As Variant

‘rv = Range(“c4”).Value
‘cv = Range(“b4″).Value

‘__________________________________USING MATCH INDEX__________________________________
‘With Application.WorksheetFunction

”Range(“D5”).Value = .Index(Range(“data_range”), .Match(rv, Range(“match_row”), 0), .Match(cv, Range(“match_col”), 0))
‘Range(“D4”).Value = .Index(Range(“data_range”), .Match(rv, Range(“match_row”), 0), .Match(cv, Range(“match_col”), 0))

‘ End With

‘Dim lastRow As Integer

‘lastRow = 4

‘__________________________________USING VLOOKUP__________________________________

‘Range(“c16:C20”).Formula = “=vlookup(b16,$j$15:$l$22,2,0)”

‘Range(“c16:C20”).FormulaR1C1 = “=vlookup(RC[-1], R16C10:R22C12,2,0)”

‘USING worksheetfunction

‘Dim i As Integer

‘For i = 16 To 20
‘Range(“c” & i).Value = Application.WorksheetFunction.VLookup(Range(“b” & i).Value, Range(“j15:l20”), 2, 0)

‘Next i

‘End Sub
‘__________________________________USING SUM__________________________________

‘Range(“C38”).Formula = “=SUM(c24:c36)”

‘using R1c1 notation

Range(“c38”).FormulaR1C1 = “=sum(R24C:R[-2]C)”
‘__________________________________USING Text/Strings in If conditions__________________________________

Range(“c43:c49”).Formula = “=IF(OR(B43=””Delhi””,B43=””mumbai””), “”india””, “”others””)”
End Sub

‘———————R1C1 notation————————————————-

‘R1C1 means row 1 column 1 ie A1 in the’ other’ notation. Well actually it means $A$1 as its an absolute address.
‘If you copy =R1C1 from any cell to any other it will always to refer to the top left cell (A1).

‘Relative references in R1C1 are done with square brackets []. so

‘R[1]C[1] means one cell down and one to the right. In fact to make it easier I did some screen shots.
‘The arrows are show dependents ones. (with hindsight, precedents might have been clearer)

‘How to use

‘define Range A2:A19 =size
‘define Range D2:D19 =qty
‘define Range B2:B19 =ord

With Application.WorksheetFunction

Range(“h2”).Value = .SumIfs(Range(“qty”), Range(“ord”), “>=” & DateSerial(2016, 9, 1), Range(“ord”), “<=” & DateSerial(2016, 9, 30))

End With

Leave a Reply