Pak Lukman,
'Pada variabel yang terdefinisi sebagai berikut :
Dim Sum_T As Range
Dim Sum_J As Range
Dim Sum_N As Range
Set Sum_T = Range("F2:F34")
Set Sum_J = Range("G1:L1")
Set Sum_N = Range("G2:L34")
on error resume next
with application.worksheetfunction
'Untuk formula worksheet berbunyi :
'>> =I2+I3
range("s3").value=range("I2").value + range("I3").value
'>> =SUM(I2:I34)
range("s4").value = .sum( range("I2:I34") )
'>> =SUMPRODUCT((F2:F34=N16)*(G1:L1=O16)*(G2:L34))
range("s15").value = .sumproduct( evaluate( "(F2:F34=N16)*(G1:L1=O16)" ) ,
sum_n )
'>> =INDEX($G$2:$M$34,MATCH($N$16,$F$2:$F$34,0),MATCH($O$16,$G$1:$M$1,0))
range("s16").value = .index( range("$G$2:$M$34") , _
.match( range("$N$16").value , range("$F$2:$F$34")
, 0 ) , _
.match( range("$O$16").value , range("$G$1:$M$1")
, 0 ) _
)
'>> =VLOOKUP($N$16,$F$2:$L$34,MATCH($O$16,$F$1:$L$1,0),FALSE)
range("s17").value = .vlookup( range("$N$16").value , _
range("$F$2:$L$34") , _
.match( range("$O$16").value ,
range("$F$1:$L$1") , 0 ) , _
0 _
)
end with
err.clear
on error goto 0
Wassalam,
Kid.
2012/11/19 lkm jktind <[email protected]>
> **
>
>
> Pak Jan.
>
> Masih belum bisa ?
> Yang berwarna biru.
>
>
>
> Option Explicit
> Sub coba_function()
> Dim Sum_T As Range
> Dim Sum_J As Range
> Dim Sum_N As Range
>
> Set Sum_T = Range("F2:F34")
> Set Sum_J = Range("G1:L1")
> Set Sum_N = Range("G2:L34")
>
> Range("R3").Formula = "=I2+I3"
> Range("R4").Formula = "=SUM(I2:I34)"
> Range("R15").Formula = "=SUMPRODUCT((F2:F34=N16)*(G1:L1=O16)*(G2:L34))"
> Range("R16").Formula =
> "=INDEX($G$2:$M$34,MATCH($N$16,$F$2:$F$34,0),MATCH($O$16,$G$1:$M$1,0))"
> Range("R17").Formula =
> "=VLOOKUP($N$16,$F$2:$L$34,MATCH($O$16,$F$1:$L$1,0),FALSE)"
>
> Range("U3").Value = Evaluate("=I2+I3")
> Range("U4").Value = Evaluate("=SUM(I2:I34)")
> Range("U15").Value =
> Evaluate("=SUMPRODUCT((F2:F34=N16)*(G1:L1=O16)*(G2:L34))")
> Range("U16").Value =
> Evaluate("=INDEX($G$2:$M$34,MATCH($N$16,$F$2:$F$34,0),MATCH($O$16,$G$1:$M$1,0))")
> Range("U17").Value =
> Evaluate("=VLOOKUP($N$16,$F$2:$L$34,MATCH($O$16,$F$1:$L$1,0),FALSE)")
>
> Range("S4") = Application.WorksheetFunction.Sum(Range(Range("I2"),
> Range("I34")))
> Range("T4") = Application.WorksheetFunction.Sum(Sum_N)
>
> 'Range("S15") =
> Application.WorksheetFunction.SumProduct((Range(Range("F2"), Range("F34"))
> = Range("N16")) * (Range(Range("G1"), Range("L1")) = Range("O16")) *
> Range(Range("G2"), Range("L34")))
> 'Range("T15") = Application.WorksheetFunction.SumProduct((Sum_T =
> Range("N16")) * (Sum_J = Range("O16")) * (Sum_N))
> 'Range("U15") = Application.WorksheetFunction.SumProduct((Range(Sum_T) =
> Range("N16")) * (Range(Sum_J) = Range("O16")) * (Range(Sum_N)))
> 'Range("S16") = Application.WorksheetFunction.Index(Range(Range("G2"),
> Range("L34")), 17, 3, 0)
> 'Range("T16") = Application.WorksheetFunction.Index(
> 'Range("U16") = Application.WorksheetFunction.Index(
> 'Range("S17") = Application.WorksheetFunction.VLookup(Range("N16"),
> (Range(Range("F2"), Range("F34"))), 4, False)
> 'Range("T17") = Application.WorksheetFunction.VLookup(Range("N16"),
> (Sum_T), 4, False)
> 'Range("U17") = Application.WorksheetFunction.VLookup(Range("N16"),
> Range(Sum_T), 4, False)
>
> Range("S18") = Application.WorksheetFunction.Match(Range("N16"),
> Range(Range("F2"), Range("F34")), 0)
> Range("T18") = Application.WorksheetFunction.Match(Range("O16"),
> Range(Range("G1"), Range("L1")), 0)
>
> End Sub
>
> Salam
>
> Lukman
>
>
