Koreksi cara 1. Atau bagian :
Sheets("MASA").Range("C" & xy)= _
worksheetfunction.Index(Sheets("BPS").Range("A3:J20"),worksheetfunction.Match(ms,
Sheets("BPS").Range("A3:A20"), 0), 10)
diganti menjadi :
Sheets("MASA").Range("C" & xy)= _
worksheetfunction.Index(Sheets("BPS").Range("j3:J20"),worksheetfunction.Match(ms,
Sheets("BPS").Range("A3:A20"), 0))
Mau cara 2. (dengan pasang formula), bagian :
'Dim hasil As String
Dim ms As Long
dim xy as Long
For xy = 2 To 13
ms = Sheets("MASA").Range("a" & xy)
if worksheetfunction.countif( Sheets("BPS").Range("A3:A20") , ms ) <>
0 then
Sheets("MASA").Range("C" & xy)= _
worksheetfunction.Index(Sheets("BPS").Range("A3:J20"),worksheetfunction.Match(ms,
Sheets("BPS").Range("A3:A20"), 0), 10)
else
Sheets("MASA").Range("C" & xy)=vbnullstring
endif
Next xy
dihapus dan diganti dengan :
with Sheets("MASA").Range("C2:C13")
.formula="=if(countif(bps!$a$3:$a$20,a2),index(bps!$j$3:$j$20,match(a2,bps!$a$3:$a$20,0)),"""")"
.parent.calculate
.value=.value
end with
Wassalam,
Kid.
On Tue, Oct 30, 2012 at 12:48 PM, Mr. Kid <[email protected]> wrote:
> Coba :
>
> 'Dim hasil As String
>
> Dim ms As Long
> dim xy as Long
>
> For xy = 2 To 13
>
> ms = Sheets("MASA").Range("a" & xy)
> if worksheetfunction.countif( Sheets("BPS").Range("A3:A20") , ms ) <>
> 0 then
> Sheets("MASA").Range("C" & xy)= _
>
> worksheetfunction.Index(Sheets("BPS").Range("A3:J20"),worksheetfunction.Match(ms,
> Sheets("BPS").Range("A3:A20"), 0), 10)
> else
> Sheets("MASA").Range("C" & xy)=vbnullstring
> endif
> Next xy
>
> Wassalam,
> Kid.
>
>
>
> 2012/10/30 him mah <[email protected]>
>
>> **
>>
>>
>> saya coba buat rumus seperti ini
>>
>> Dim hasil As String
>> Dim ms As Long
>> dim xy as Long
>>
>> For xy = 2 To 13
>> ms = Sheets("MASA").Range("a" & xy)
>> hasil = Application.Index(Sheets("BPS").Range("A3:J20"),
>> Application.Match(ms, Sheets("BPS").Range("A3:A20), False), 10)
>> If IsError(hasil) Then hasil = "" Else hasil = hasil
>>
>> Sheets("MASA").Range("C" & xy) = hasil
>> Next xy
>>
>> ketika datanya tidak ditemukan di sheets BPS masa akan muncum run time
>> error 13
>>
>> kemudian kalau ditambahkan resume next untuk data yang tidak ada
>> hasilnya adalah data sebelumnya
>>
>> kalau yang seperti ini pemesahannya gimana ya
>>
>>
>
>
