Pak Kid.
sudah bisa
terima kasih untuk Pak Kid dan Pak Jan Raisin
Dim Sum_T As Range
Dim Sum_J As Range
Dim Sum_N As Range
Set Sum_T = Range("F1:F34")
Set Sum_J = Range("F1:L1")
Set Sum_N = Range("F1: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("(F1:F34=N16)*(F1:L1=O16)"),
Sum_N)
Range("T15").Value = .SumProduct(Evaluate("(Sum_T = Cells(16, 14)) * (Sum_J
= Cells(16, 15))"), Sum_N) '-- cuma yg ini nga bisa. tapi nga apa apa.
Range("U15") = .SumProduct(Evaluate("(F1:F34=N16)*(F1: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("$F$1:$L$34"),
.Match(Range("$N$16").Value, Range("$F$1:$F$34"), 0),
.Match(Range("$O$16").Value, Range("$F$1:$L$1"), 0))
Range("T16").Value = .Index(Sum_N, .Match(Range("$N$16").Value, Sum_T, 0),
.Match(Range("$O$16").Value, Sum_J, 0))
Range("U16") = .Index(Sum_N, .Match(Range("$N$16"), Sum_T, 0),
.Match(Range("$O$16"), Sum_J, 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$1:$L$34"),
.Match(Range("$O$16").Value, Range("$F$1:$L$1"), 0), 0)
Range("T17").Value = .VLookup(Range("$N$16").Value, Sum_N,
.Match(Range("$O$16").Value, Sum_J, 0), 0)
Range("U17") = .VLookup(Range("$N$16"), Sum_N, .Match(Range("$O$16"),
Sum_J, 0), 0)
Range("S19").Value = .WorkDay(Range("N19"), Range("O19"), Range("AA2:AA39"))
End With
Err.Clear
On Error GoTo 0
Akan saya coba untuk function lain nya sesuai dengan kebutuhanku.
Salam dan selamat malam
Lukman
On Mon, Nov 19, 2012 at 7:44 PM, Mr. Kid <[email protected]> wrote:
> **
>
>
> 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
>>
>>
>
>
