Ok saya coba dulu ya Pak Slamet
Maaf mungkin penjelasan saya kurang dimengerti
tks
ca2
--- Pada Sab, 20/6/09, Slamet Harto <slametha...@wiendi.web.id> menulis:
Dari: Slamet Harto <slametha...@wiendi.web.id>
Topik: Re: [belajar-excel] autofill dengan macro excel
Kepada: belajar-excel@yahoogroups.com
Tanggal: Sabtu, 20 Juni, 2009, 5:46 PM
untuk kasus yang di kirim ke saya, dengan pertanyaan "pada kolom b1
saya ingin melakukan autofill dgn macro excel tetapi kadang baris
akhir dari kolom a tidak selalu di a10"
maksudnya jika data akhir ada jeda baris ya?
Option Explicit
Sub Sim_Sa_la_Bim_ PAK_PAK_PAK( )
Dim LastRow As Long, x As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp) .Row
With Range("B1"). Resize(LastRow)
.Formula = "=IF(RC[-1]= """","""" ,RC[-1]*5) "
End With
End Sub
Sedangkan untuk contoh lampiran kasus yang dikirim ke mba Siti saya
tidak menggunakan Autofill, sayangnya implementasi if function nya
kurang tepat.. sampai-sampai mba siti aja bingung maksud formula if
tsb.
Option Explicit
Sub CopyFormulaDown1( )
Dim i As Long
Dim rCell As Range, rng As Range
Set rng = Range(Range( "A2"), Range("A2"). End(xlDown) )
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each rCell In rng
If (Left(rCell, 2)) = "LM" Then
rCell.Offset( 0, 1) = "Darurat Macet"
ElseIf (Left(rCell, 2)) = "RM" Then
rCell.Offset( 0, 1).Value = "Rumah Macet"
ElseIf (Left(rCell, 1)) = "R" Then
rCell.Offset( 0, 1).Value = "Rumah"
ElseIf (Left(rCell, 1)) = "L" Then
rCell.Offset( 0, 1).Value = "Darurat"
Else
rCell.Offset( 0, 1).Value = "Hayoo apa??"
End If
Next rCell
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutoma tic
End With
End Sub
Sub CopyFormulaDown2( )
Dim lastrow As Long, i As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Worksheets(" Bgr")
lastrow = .Cells(.Rows. Count, "A").End(xlUp) .Row
For i = 2 To lastrow
If (Left(.Cells( i, "A"), 2)) = "RM" Then
.Cells(i, "B").Value = "Rumah Macet"
ElseIf (Left(.Cells( i, "A"), 2)) = "LM" Then
.Cells(i, "B").Value = "Darurat Macet"
ElseIf (Left(.Cells( i, "A"), 1)) = "L" Then
.Cells(i, "B").Value = "Darurat"
ElseIf (Left(.Cells( i, "A"), 1)) = "R" Then
.Cells(i, "B").Value = "Rumah"
Else
.Cells(i, "B").Value = "Hayoo Apa??"
End If
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutoma tic
End With
End Sub
Wassalam
Akses email lebih cepat. Yahoo! menyarankan Anda meng-upgrade browser ke
Internet Explorer 8 baru yang dioptimalkan untuk Yahoo! Dapatkan di sini!
http://downloads.yahoo.com/id/internetexplorer
