Quantcast

[belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
Assalaamu alaikum,
to the point saja.
saya ingin mengcopy range bernama yang berisi formula ke kolom (range ) di kanan cell input.
setelah tercopy pada range target,hasilnya di range target di paste special value . jadi yang ada tinggal nilai hasilnya saja tanpa formula.
bagaimana code macronya.
ilustrasi ada di attachment.
terima kasih sebelumnya.

wassalam

Ghozi Alkatiri

copy range berisi formula ke kolom di kanan input.xlsx (14K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

Kid
Administrator
Wa alaikumussalam wr. wb.

Prinsip mengcopy paste special :
range_yang_akan _di_copy.copy
range_tujuan.pastespecial xlpastevalues

Dimana harus meletakkannya ?
1. ketika diharapkan setiap usai suatu event (seperti ubah nilai, aktifkan
sebuah cell (pindah yang diklik) dsb), maka letakkan pada event yang
bersangkutan.
Setiap object dari class worksheet maupun workbook, dan juga dari object
control activex, memiliki event yang bisa ditangkap
seperti event ketika nilai cell berubah, bisa ditangkap melalui prosedur
event Worksheet_change() di worksheet tersebut

2. ketika diharapkan ketika sebuah shape selain activex control di klik
maka akan mengerjakan sesuatu, atau dipanggil dari daftar macro yang ada di
file,
maka letakkan pada sebuah module.

Pertanyaan berikutnya, bagaimana caranya membuat sebuah prosedur yang
berisi rangkaian kalimat perintah program ?
1. untuk event :
double click object yang memiliki event
pilih eventnya pada combo box yang disediakan di VBE (tekan Alt F11 untuk
ke VBE). Combo kiri memilih object (worksheet, workbook, form, dsb)
Combo kanan akan berisi event yang dimiliki si object.

Contoh :
double click tulisan sheet1 (sheets 1) di VBA Project (project explorer
frame kiri atas)
lihat combo kiri di frame kanan, pilih worksheet
lihat combo kanan di frame kanan, pilih event change
maka akan muncul blok prosedur didahului deklarasi misal Private Sub
Worksheet_Change()
Private adalah scope, Sub adalah jenis prosedur, Worksheet_Change() adalah
nama prosedur

diakhiri oleh End Sub karena jenis prosedur adalah Sub

Diantara dua baris itu ada ruang, maka isi rangkaian kalimat perintah
disana.

2. untuk module :
ke VBE, insert Module, tulis nama prosedur, misal :
Public Sub ContohProsedur()
     'tulis rangkaian perintah disini
End Sub

lalu di worksheet (Alt F11 untuk kembali ke sheet), insert sebuah shape
(bukan activex control), klik kanan dan pilih Assign Macro, muncul dialog,
pilih nama prosedur yang akan dijalankan oleh si shape (misal prosedur
contoh tadi, maka pilih ContohProsedur

Pertanyaan berikutnya, bagaimana mengimplementasikan :
range_yang_akan _di_copy.copy
range_tujuan.pastespecial xlpastevalues
?

Misal : range yang akan di-copy adalah A1:D1
range paste ada di A11:D11

Range("A1:D1").copy
Range("a11:d11").pastespecial xlpastevalues

Kalau pakai nama range bagaimana ?
Range("nama rangenya apa tulis aja disini").copy
Range("a11:d11").pastespecial xlpastevalues

Kalau gak tahu lokasinya secara persis bagaimana ?
jawabnya : ya dicari persisnya

Untuk hal ini, perlu memahami tentang hal bernama variabel, mulai dari cara
mendefinisikan , mengisi nilai, menghapus nilai, atau bahkan membuang
variabel
Misal : tabel tujuan mulai di range A9 (header kiri atas tabel hasil)

dim rngTabel as range    'artinya : siapkan ruang berupa range bernama
rngTabel

set rngTabel = range("a9").currentregion      'isi rngtabel dengan sebuah
area range yang melingkupi si range a9

range("a1:d11").copy      'copy range ke clipboard
rngtabel.offset(  rngtabel.rows.count ).resize( 1 ).pastespecial
xlpastevalues     'udah mahir dengan offset di formula worksheet bukan,

'ini mirip fungsi offset di worksheet,

'ada lompat [ .Offset ] dan ubah luasan [  .Resize ]

Kid.

2011/12/17 ghozi alkatiri <[hidden email]>

> **
>
>
> Assalaamu alaikum,
> to the point saja.
> saya ingin mengcopy range bernama yang berisi formula ke kolom (range ) di
> kanan cell input.
> setelah tercopy pada range target,hasilnya di range target di paste
> special value . jadi yang ada tinggal nilai hasilnya saja tanpa formula.
> bagaimana code macronya.
> ilustrasi ada di attachment.
> terima kasih sebelumnya.
>
> wassalam
>
> Ghozi Alkatiri
>
>  
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

iHaps
In reply to this post by ghozi alkatiri
wa'alaikum salam

mudah mudahan berikut ini tidak terlau jauh dari yg dimaksud pak ghozi


Sub Cara_Satu()
   Dim r As Long, n As Long
   ' cara ini masih berorientasi formula di sheet
   ' metoda mensecel dan mengcopy range itu
   ' memerlukan waktu yg lebih lama (lamban)

   ' mengkopy formula (cara kalkulasinya)
   ' etapi diterapkan ke baris lain
   ' hasilnya is cell berupa formula
   Range("B1:D1").Copy
   ' mengulang pengcopyan selama di A (mulai A10 ada datanya)
   r = 1
   Do While Len(Range("A10").Cells(r, 1)) > 0
      Range("B10").Cells(r, 1).PasteSpecial Paste:=xlPasteFormulas
      r = r + 1
   Loop

   ' mengkonversi formula hasil copyan menjadi konstanta
    Range("B10:D10").Resize(r, 3).Copy
    Range("B10").PasteSpecial Paste:=xlPasteValues

   ' menghilangkan memory range yg dicopy
   Application.CutCopyMode = False

End Sub


*Sub Cara_Dua()*

   ' cara ini harus mengetahui dulu
   ' logika dan kalkuasi yg ada di baris contoh
   ' kemudian langsung menerapkan kalkulasi dan logika ke cell
   ' Kalkulasi dilakukan di VBA
   ' hasil kalkulasi ditulis di cells

   Dim RefRg As Range, r As Long

   Set RefRg = Range(Range("A10"), Range("A10").End(xlDown))

   ' mengulang pekerjaan pada tiap baris mulai A10
   For r = 1 To RefRg.Rows.Count
      RefRg(r, 2) = Mid(RefRg(r, 1), 1, 2)
      RefRg(r, 3) = Mid(RefRg(r, 1), 3, 2)
      RefRg(r, 4) = Mid(RefRg(r, 1), 5, 2)
   Next
End Sub

menjalankam makro  tekan Alt + F8, pilih nama makro > RUN




2011/12/17 ghozi alkatiri <[hidden email]>

> **
> Assalaamu alaikum,
> to the point saja.
> saya ingin mengcopy range bernama yang berisi formula ke kolom (range ) di
> kanan cell input.
> setelah tercopy pada range target,hasilnya di range target di paste
> special value . jadi yang ada tinggal nilai hasilnya saja tanpa formula.
> bagaimana code macronya.
> ilustrasi ada di attachment.
> terima kasih sebelumnya.
>
> wassalam
>
> Ghozi Alkatiri
>
>
>

copy range berisi formula ke kolom di kanan input (iHaps).xlsm (24K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
terima kasih atas tanggapan dari mr kid dan mbak haps, semuanya akan saya simpan sebagai referensi dan akan saya pelajari lebih lanjut.
khusus mengenai tanggapan dari mbak haps sudah mendekati yang saya inginkan .
cuma yang saya inginkan adalah proses satu demi satu  tidak secara keseluruhan seperti yang dicontohkan mbak haps.
jelasnya begini
misalkan saya menginput nilai ABCDEF di A10 maka ketika saya enter akan terjadi proses pengcopyan dari range formula yang sudah disiapkan. dan meninggalkan nilai valuenya saja di  AB,CD dan EF di cell C10, D10 dan  E10
demikian juga ketika saya lanjutkan dengan  baris berikutnya. proses pengcopian akan terjadi sesaat setelah saya menekan enter. tanpa harus menekan run macro atau klik tombol.

wassalam

Ghozi Alkatiri

________________________________
 Dari: Haps <[hidden email]>
Kepada: [hidden email]
Dikirim: Sabtu, 17 Desember 2011 20:17
Judul: Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 

 
wa'alaikum salam

mudah mudahan berikut ini tidak terlau jauh dari yg dimaksud pak ghozi



Sub Cara_Satu()
   Dim r As Long, n As Long
   ' cara ini masih berorientasi formula di sheet
   ' metoda mensecel dan mengcopy range itu
   ' memerlukan waktu yg lebih lama (lamban)
   
   ' mengkopy formula (cara kalkulasinya)
   ' etapi diterapkan ke baris lain
   ' hasilnya is cell berupa formula
   Range("B1:D1").Copy
   ' mengulang pengcopyan selama di A (mulai A10 ada datanya)
   r = 1
   Do While Len(Range("A10").Cells(r, 1)) > 0
      Range("B10").Cells(r, 1).PasteSpecial Paste:=xlPasteFormulas
      r = r + 1
   Loop
   
   ' mengkonversi formula hasil copyan menjadi konstanta
    Range("B10:D10").Resize(r, 3).Copy
    Range("B10").PasteSpecial Paste:=xlPasteValues
      
   ' menghilangkan memory range yg dicopy
   Application.CutCopyMode = False

End Sub


Sub Cara_Dua()

   ' cara ini harus mengetahui dulu
   ' logika dan kalkuasi yg ada di baris contoh
   ' kemudian langsung menerapkan kalkulasi dan logika ke cell
   ' Kalkulasi dilakukan di VBA
   ' hasil kalkulasi ditulis di cells
   
   Dim RefRg As Range, r As Long
   
   Set RefRg = Range(Range("A10"), Range("A10").End(xlDown))
   
  ' mengulang pekerjaan pada tiap baris mulai A10
   For r = 1 To RefRg.Rows.Count
      RefRg(r, 2) = Mid(RefRg(r, 1), 1, 2)
      RefRg(r, 3) = Mid(RefRg(r, 1), 3, 2)
      RefRg(r, 4) = Mid(RefRg(r, 1), 5, 2)
   Next
End Sub

menjalankam makro  tekan Alt + F8, pilih nama makro > RUN




2011/12/17 ghozi alkatiri <[hidden email]>

 

>Assalaamu alaikum,
>to the point saja.
>saya ingin mengcopy range bernama yang berisi formula ke kolom (range ) di kanan cell input.
>setelah tercopy pada range target,hasilnya di range target di paste special value . jadi yang ada tinggal nilai hasilnya saja tanpa formula.
>bagaimana code macronya.
>ilustrasi ada di attachment.
>terima kasih sebelumnya.
>
>
>wassalam
>
>
>Ghozi Alkatiri
>
>
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

Edy Wiyono
Waalaikumsalam, Pak Ghozi
Saya menggunakan vba untuk file contohnya.
Mudah-mudahan bisa membantu.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Sheets("sheet1").Activate Then
       If ActiveCell.Column = 1 Then
          If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
                  a = ActiveCell.Row
                  b = ActiveCell.Column

                  If a = Cells(1, 100) And b = Cells(1, 101) Then
                    Exit Sub
                  End If

                  Range("formula").Select
                  Selection.Copy
                  Cells(a - 1, b + 1).Select
                  Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                  Selection.Copy
                  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                  Cells(1, 100) = a
                  Cells(1, 101) = b
                  Cells(a, b).Select
                  Application.CutCopyMode = False
                  ActiveWorkbook.Save

          End If
       End If
    End If
End Sub





From: [hidden email]
On Behalf Of ghozi alkatiri
Sent: Saturday, December 17, 2011 8:34 PM

terima kasih atas tanggapan dari mr kid dan mbak haps, semuanya akan saya simpan sebagai referensi dan akan saya pelajari lebih lanjut.

khusus mengenai tanggapan dari mbak haps sudah mendekati yang saya inginkan .

cuma yang saya inginkan adalah proses satu demi satu  tidak secara keseluruhan seperti yang dicontohkan mbak haps.

jelasnya begini

misalkan saya menginput nilai ABCDEF di A10 maka ketika saya enter akan terjadi proses pengcopyan dari range formula yang sudah disiapkan. dan meninggalkan nilai valuenya saja di  AB,CD dan EF di cell C10, D10 dan  E10

demikian juga ketika saya lanjutkan dengan  baris berikutnya. proses pengcopian akan terjadi sesaat setelah saya menekan enter. tanpa harus menekan run macro atau klik tombol.

wassalam

Ghozi Alkatiri

  _____

Dari: Haps <[hidden email]>
Dikirim: Sabtu, 17 Desember 2011 20:17
Sub Cara_Satu()
Sub Cara_Dua()

coba-revisi.xlsm (23K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
terima kasih atas tanggapannya. 
sebenanrnya solusi yang bapak kirimkan hampir mendekati yang saya inginkan tapi rupanya ada kesalahan teknis pada hasil di kolom ke dua dan ke tiga dari hasil yang  diharapkan.
misalkan saya menginput data 123456 di kolom A hasil yang saya inginkan adalah kopi formula dari range rujukan
seharusnya menghasilkan nilai di kolom B 12 di kolom C 34 dan di kolom D 56 tapi pada  kenyataanya yang benar hanya hasil pada kolom B saja. sedangkan di kolom B dan C nilai apapun yang dimasukkan hasilnya selalu  cd dan ef.  
kalau tidak merepotkan saya tunggu koreksinya.
sekali lagi terima kasih.

wassalam


________________________________
 Dari: Edy Wiyono <[hidden email]>
Kepada: [hidden email]
Dikirim: Minggu, 18 Desember 2011 17:51
Judul: RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 

 
Waalaikumsalam, Pak Ghozi
Saya menggunakan vba untuk file contohnya.
Mudah-mudahan bisa membantu.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("sheet1").Activate Then
If ActiveCell.Column = 1 Then
If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
a = ActiveCell.Row
b = ActiveCell.Column

If a = Cells(1, 100) And b = Cells(1, 101) Then
Exit Sub
End If

Range("formula").Select
Selection.Copy
Cells(a - 1, b + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Cells(1, 100) = a
Cells(1, 101) = b
Cells(a, b).Select
Application.CutCopyMode = False
ActiveWorkbook.Save

End If
End If
End If
End Sub

From: [hidden email]
On Behalf Of ghozi alkatiri
Sent: Saturday, December 17, 2011 8:34 PM

terima kasih atas tanggapan dari mr kid dan mbak haps, semuanya akan saya simpan sebagai referensi dan akan saya pelajari lebih lanjut.

khusus mengenai tanggapan dari mbak haps sudah mendekati yang saya inginkan .

cuma yang saya inginkan adalah proses satu demi satu  tidak secara keseluruhan seperti yang dicontohkan mbak haps.

jelasnya begini

misalkan saya menginput nilai ABCDEF di A10 maka ketika saya enter akan terjadi proses pengcopyan dari range formula yang sudah disiapkan. dan meninggalkan nilai valuenya saja di  AB,CD dan EF di cell C10, D10 dan  E10

demikian juga ketika saya lanjutkan dengan  baris berikutnya. proses pengcopian akan terjadi sesaat setelah saya menekan enter. tanpa harus menekan run macro atau klik tombol.

wassalam

Ghozi Alkatiri

_____

Dari: Haps <[hidden email]>
Dikirim: Sabtu, 17 Desember 2011 20:17
Sub Cara_Satu()
Sub Cara_Dua()

 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

iHaps
Pak Ghozi ysh,

Berikut ini dua cara makro

*A *
*(mengenai cara yg ditempuh untuk menuju tercapainya hasil (algoritma) *

Cara 1
tetap menggunakan action/indakan copy paste cell / range / formula
dari range contoh (yg sudah diisi contoh formula), ke  range TARGET
a. copy formulanya (ke Target)
b hasil copy >> di copy VALUESnya ke dirinya (Target) sendiri

Cara 2.
tidak memerlukan range contoh formula tetapi memerlukan
informasi logika dan kalkulasi yg dikehendaki (sering disebut :* expressi*)

jadi makronya tidak melakukan tindakan copy paste object cell, melainkan
langsung
mengAssigned beberapa buah cell dengan hasil* Expressi* tadi.


*B *
*(Mengenai Bagaimana, kapan Makro harus dijalankan / jalan otomatis)*

Hal kedua adalah mengatur KAPAN /&berdasarkan apa MAKRO MAU OTOMATIS JALAN
Dikehendaki : Makro Jalan ketika data DI-INPUTKAN ke Range INPUT (yaitu
Range A10:A65536,
kalau excelnya = XL2003)

Hal seperti ini di VBA maupun VB dikenal sebagai memanfaatkan EVENTs
Kebetulan : jika suatu cell di suatu sheet diubah VALUES nya (dengan cara
apapun, CMIIW)
maka akan terjadi Event yg benama Sheet_Change

Nah oleh sebab itulah makro harus ditulis di dalam *PROSEDUR EVENT
Sheet_Change*
(Prosedur yg memanfaatkan adanya perubahan yg terjadi pada sheet)

Untuk menjalankannya pun sering kita harus memberi BATASAN / Syarat, dengan
tujuan
agar makro tidak semena-mena / semaunya / kapan saja jalan:  jika terjadi
event
(Padahal setiap kita menyentuh sheet sebagian sentuhan itu berupa Events)

Di dalam kedua makro (yg ditulis di module sheet 1 dan module Sheet2 )
itu, kita
*memberi *
*batasan: Makro hanya boleh jalan JIKA:*

* Target ( = rangel yg berubah nilainya) = hanya berupa cell tunggal
* Kolom yg ditempati Target harus KOLOM 1 (kolom A)
* Baris yg ditempati Target harus di dibawah (lnilai row-nya ebih besar)
daripada BARIS 9
* Target harus bukan cell kosong
(jika target = cell kosong; makro tidak mengisi hasil tetapi diatur agar
mengosongkan 3 cell di kanannya)


Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' assignment hasil expressi ke suatu cell/range
   '---------------------------------------------
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               Range(Target(1, 2), Target(1, 4)).NumberFormat = "@"
               Target(1, 2) = Mid(Target.Value, 1, 2)
               Target(1, 3) = Mid(Target.Value, 3, 2)
               Target(1, 4) = Mid(Target.Value, 5, 2)
               Application.CutCopyMode = False
            Else
               Range(Target(1, 2), Target(1, 4)).ClearContents
            End If
         End If
      End If
   End If
End Sub

'--------------


Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' CARA 2  : copy paste cells/formula
   '-----------------------------------
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               *Range("B1:D1")*.Copy
               Target(1, 2).PasteSpecial xlPaste*Formulas*AndNumberFormats
               Target(1, 2).Resize(1, 3).Copy
               Target(1, 2).Resize(1, 3).PasteSpecial xlPaste*Values*
            Else
               Target(1, 2).Resize(1, 3).ClearContents
            End If
         End If
      End If
   End If
End Sub
'------------------

Demikain Pak, mudah-mudahan menjadi semakin jelas.

Oiya, dlm bermakro, seringkali kita harus mempertimbangkan kecepatan
jalannya makro
(runningttimenya).
Seperti kemarin telah dibahas, tindakan select / copy / paste terhadap
cells / range itu cukup
memerlukan waktu. Oleh karena itu, jika prosedur mengandung banyak tugas,
kita lebih baik
menghindari cara /tindakan select / copy / paste cells tsb.
(dan itulah yg kita tunjukkan sebagai perbedaan di kedua makro di atas )

Wassalamu 'alaikum wr wb
i Haps.




2011/12/18 ghozi alkatiri <[hidden email]>

> **
> terima kasih atas tanggapannya.
> sebenanrnya solusi yang bapak kirimkan hampir mendekati yang saya inginkan
> tapi rupanya ada kesalahan teknis pada hasil di kolom ke dua dan ke tiga
> dari hasil yang  diharapkan.
> misalkan saya menginput data 123456 di kolom A hasil yang saya inginkan
> adalah kopi formula dari range rujukan
> seharusnya menghasilkan nilai di kolom B 12 di kolom C 34 dan di kolom D
> 56 tapi pada  kenyataanya yang benar hanya hasil pada kolom B saja.
> sedangkan di kolom B dan C nilai apapun yang dimasukkan hasilnya selalu  cd
> dan ef.
> kalau tidak merepotkan saya tunggu koreksinya.
> sekali lagi terima kasih.
>
> wassalam
>
>   ------------------------------
> *Dari:* Edy Wiyono <[hidden email]>
> *Dikirim:* Minggu, 18 Desember 2011 17:51
>
> Waalaikumsalam, Pak Ghozi
> Saya menggunakan vba untuk file contohnya.
> Mudah-mudahan bisa membantu.
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Sheets("sheet1").Activate Then
> If ActiveCell.Column = 1 Then
> If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
> a = ActiveCell.Row
> b = ActiveCell.Column
>  If a = Cells(1, 100) And b = Cells(1, 101) Then
> Exit Sub
> End If
>
> Range("formula").Select
> Selection.Copy
> Cells(a - 1, b + 1).Select
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
>
> Cells(1, 100) = a
> Cells(1, 101) = b
> Cells(a, b).Select
> Application.CutCopyMode = False
> ActiveWorkbook.Save
>
> End If
> End If
> End If
> End Sub
>

Mengisi 3 cells di sebelah kanan INPUT dengan hasil Expresi (iHaps).xlsm (32K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
terima kasih mbak haps.
akhirnya dengan modifikasi pada beberapa baris kode dengan referensi yang sudah diberikan mbak haps dan beberpa kawan lainnya saya berhasil mendapatkan yang saya harapkan.
untuk koreksi di bawah ini saya tuliskan  code dari mbak haps dan modifikasi yang saya lakukan.


kode dari mbak haps

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' CARA 2  : copy paste cells/formula
   '-----------------------------------
   Dim ContohRumus As Range
   Set ContohRumus = Range("B1:D1")
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               ContohRumus.Copy
               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
               Application.CutCopyMode = False
               ActiveCell.Offset(1, -1).Range("A1").Select
            Else
               Target(1, 2).Resize(1, 3).ClearContents
            End If
         End If
      End If
   End If
End Sub

modifikasi Ghozi
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' CARA 2  : copy paste cells/formula
   '-----------------------------------
   Dim formula As Range
   Set formula = Range("B1:D1")
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               formula.Copy
               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
               Application.CutCopyMode = False
               ActiveCell.Offset(1, -1).Range("A1").Select
            Else
               Target(1, 2).Resize(1, 3).ClearContents
            End If
         End If
      End If
   End If
End Sub

hasil dari modifikasi ini sangat pas dengan yang saya inginkan. tetapi karena saya masih sangat awam sekali tentang macro saya ingin mendapatkan koreksi dari mbak haps dan teman teman lainnya  apa kode hasil modifikasi di atas ini sudah aman untuk digunakan tanpa efek samping saya tunggu tanggapannya.

pertanyaan tambahan 
bagaimana kalau dalam set hasil copy juga di pastekan format dari range asal 
jadi yang akan tercopy di samping nilai hasil formula juga format dari range asal misalkan border warna dll.
ini efisiensi supaya tidak menggunakan conditional formating.

wassalam

Ghozi Alkatiri





________________________________
 Dari: i Haps <[hidden email]>
Kepada: [hidden email]
Dikirim: Senin, 19 Desember 2011 15:48
Judul: Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 

 
Pak Ghozi ysh,

Berikut ini dua cara makro


(mengenai cara yg ditempuh untuk menuju tercapainya hasil (algoritma) 

Cara 1
tetap menggunakan action/indakancopy paste cell / range / formula
dari range contoh (yg sudah diisi contoh formula), ke  range TARGET
a. copy formulanya (ke Target)
b hasil copy >> di copy VALUESnya ke dirinya (Target) sendiri 

Cara 2.
tidak memerlukan range contoh formula tetapi memerlukan
informasi logika dan kalkulasi yg dikehendaki (sering disebut :expressi)

jadi makronya tidak melakukan tindakan copy paste object cell, melainkan langsung
mengAssignedbeberapa buah cell dengan hasilExpressi tadi.



(Mengenai Bagaimana, kapan Makro harus dijalankan / jalan otomatis)

Hal kedua adalah mengatur KAPAN /&berdasarkan apa MAKRO MAU OTOMATIS JALAN 
Dikehendaki : Makro Jalan ketika data DI-INPUTKAN ke Range INPUT (yaitu Range A10:A65536,  
kalau excelnya = XL2003)

Hal seperti ini di VBA maupun VB dikenal sebagai memanfaatkan EVENTs
Kebetulan : jika suatu cell di suatu sheet diubah VALUES nya (dengan cara apapun, CMIIW)
maka akan terjadi Event yg benama Sheet_Change

Nah oleh sebab itulah makro harus ditulis di dalam PROSEDUR EVENT Sheet_Change
(Prosedur yg memanfaatkan adanya perubahan yg terjadi pada sheet)

Untuk menjalankannya pun sering kita harus memberi BATASAN / Syarat, dengan tujuan
agar makro tidak semena-mena / semaunya / kapan saja jalan:  jika terjadi event
(Padahal setiap kita menyentuh sheet sebagian sentuhan itu berupa Events)

Di dalam kedua makro (yg ditulis di module sheet 1 dan module Sheet2 ) itu, kita memberi 
batasan: Makro hanya boleh jalan JIKA:

* Target ( = rangel yg berubah nilainya) = hanya berupa cell tunggal
* Kolom yg ditempati Target harus KOLOM 1 (kolom A)
* Baris yg ditempati Target harus di dibawah (lnilai row-nya ebih besar) daripada BARIS 9
* Target harus bukan cell kosong
(jika target = cell kosong; makro tidak mengisi hasil tetapi diatur agar mengosongkan 3 cell di kanannya)


Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' assignment hasil expressi ke suatu cell/range
   '---------------------------------------------
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               Range(Target(1, 2), Target(1, 4)).NumberFormat = "@"
               Target(1, 2) = Mid(Target.Value, 1, 2)
               Target(1, 3) = Mid(Target.Value, 3, 2)
               Target(1, 4) = Mid(Target.Value, 5, 2)
               Application.CutCopyMode = False
            Else
               Range(Target(1, 2), Target(1, 4)).ClearContents
            End If
         End If
      End If
   End If
End Sub

'--------------


Private Sub Worksheet_Change(ByVal Target As Range)
   ' iHaps // 19 dec , 2001
   ' CARA 2  : copy paste cells/formula
   '-----------------------------------
   If Target.Count = 1 Then
      If Target.Column = 1 Then
         If Target.Row > 9 Then
            If Not Target.Value = vbNullString Then
               Range("B1:D1").Copy
               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
               Target(1, 2).Resize(1, 3).Copy
               Target(1, 2).Resize(1, 3).PasteSpecial xlPasteValues
            Else
               Target(1, 2).Resize(1, 3).ClearContents
            End If
         End If
      End If
   End If
End Sub
'------------------

Demikain Pak, mudah-mudahan menjadi semakin jelas.

Oiya, dlm bermakro, seringkali kita harus mempertimbangkan kecepatan jalannya makro 
(runningttimenya).
Seperti kemarin telah dibahas, tindakan select / copy / paste terhadap cells / range itu cukup 
memerlukan waktu. Oleh karena itu, jika prosedur mengandung banyak tugas, kita lebih baik 
menghindari cara /tindakan select / copy / paste cells tsb.
(dan itulah yg kita tunjukkan sebagai perbedaan di kedua makro di atas )

Wassalamu 'alaikum wr wb
i Haps.





2011/12/18 ghozi alkatiri <[hidden email]>

 

>terima kasih atas tanggapannya. 
>sebenanrnya solusi yang bapak kirimkan hampir mendekati yang saya inginkan tapi rupanya ada kesalahan teknis pada hasil di kolom ke dua dan ke tiga dari hasil yang  diharapkan.
>misalkan saya menginput data 123456 di kolom A hasil yang saya inginkan adalah kopi formula dari range rujukan
>seharusnya menghasilkan nilai di kolom B 12 di kolom C 34 dan di kolom D 56 tapi pada  kenyataanya yang benar hanya hasil pada kolom B saja. sedangkan di kolom B dan C nilai apapun yang dimasukkan hasilnya selalu  cd dan ef.  
>kalau tidak merepotkan saya tunggu koreksinya.
>sekali lagi terima kasih.
>
>
>wassalam
>
>
>
>________________________________
> Dari: Edy Wiyono <[hidden email]>
>Dikirim: Minggu, 18 Desember 2011 17:51
>

>Waalaikumsalam, Pak Ghozi
>Saya menggunakan vba untuk file contohnya.
>Mudah-mudahan bisa membantu.
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>If Sheets("sheet1").Activate Then
>If ActiveCell.Column = 1 Then
>If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
>a = ActiveCell.Row
>b = ActiveCell.Column
> If a = Cells(1, 100) And b = Cells(1, 101) Then
>Exit Sub
>End If
>
>Range("formula").Select
>Selection.Copy
>Cells(a - 1, b + 1).Select
>Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>Selection.Copy
>Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>
>Cells(1, 100) = a
>Cells(1, 101) = b
>Cells(a, b).Select
>Application.CutCopyMode = False
>ActiveWorkbook.Save
>
>End If
>End If
>End If
>End Sub
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

iHaps
Ysh pak Ghozi,

*( A )*

Ada hal yg perlu mendapat perhatian; yaitu object Range : *TARGET*  dan *
ActiveCell*
Keduanya (dalam Event Sheet_Change)  punya address yg berbeda, bisa berbeda
jauh &
tidak tertentu perbandingan jraknya

Jika target adalah sudah FIXED yaitu *cell yg berubah *
Misalnya cell B4 di ubah dari kosong menjadi berisi data "X"
maka yg disebut sebagai TARGET (objet Range) adalah cell B4

Sedangkan *ActiveCell* adalah *cell dimana cellpointer berda ketika terjadi
perubahan.*
(*perubahan dianggap terjadi* bila *cell sudah diENTER atau cell pointer
telah meninggalkan cell target*.)
Hal ini sering lupt dari perhatian penulis VBA Excel di tahap-tahap awal.

Kita ketahui bahwa di *[ Excel Option ]* bisa diset  gerakan cell ketika
kita mengenter pengisian cell
bisa  = DOWN / ke CELL DI BAWAHnya  bisa RIGHT / ke CELL DI KANANNYA
Tentunya emakai makro kita tidak bisa kita paksa untuk menyeragamkan
setting tsb.

Selain itu, Malah kita bisa* menyudahi penulisan di cell dengan mengklik
CELL LAIN mana saja*
yang mungkin JAUH sekali dari cell TARGET dan tempatnya pun tidak terduga !!

Jika sudah kita ketahui seperti itu keadaannya, maka pemakaiam Object *
ActiveCell*
rasanya tidak aman lagi, karena "beada di mana-kah ActiveCell saat itu",
 kita tidak bisa menduganya.

Maka itu di kedua Prosedur yg Haps kirimkan tadi,  tidak ada keyword
Activecell, yang ada hanya Target
yaitu demi *amannya (yakinnya) letak dan Dimensi Range* yg akan
diolah/proses,

Modifikasi dari pak Ghozi
  ActiveCell.Offset(1, -1).Range("A1").Select
jika maksudnya *menSelect INPUT cell berikutnya* (cell yg berada di bawah
target yg baru saja
selesai diisi);  mungkin bisa diganti dengan kode seperti ini
  Target(2,1).Select
(ingat bahwa object Range Target sendiri (jika ia berupa cell tunggal) bisa
disebut dengan *Target(1,1)*
Dua buah parameter itu adalah index untuk baris dan kolom dilihat dari
titik pusatnya = Target)

Terlampir makro yg membuktikan bahwa Aktivecell *bisa tidak FIXED jaraknya
jika dilihat dari TARGET*
karena 3 (tiga) alasan tadi (bisa di kanannya, bisa dibawahnya, dan kalau
user mengakhiri pengisian cell
dengan mengklik cell lain mana saja, maka ActiveCell bisa di mana saja)

----ditulis di Module Sheet----
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count = 1 Then
      MsgBox "TARGET  = " & Target.Address & vbCrLf & _
           "CellAktif = " & ActiveCell.Address
   End If
End Sub

adakan percobaan:
1. mengedit cell B4 / mengisi cell B4 dengan data baru
2* lalu di enter*
3. lihat MsgBox

4 ubah setting di* excel option* (mengenai arah berpindahnya cell ketika
dienter (Down / Right)
5 ulangi 1 s/d 3
6 ulangi 1
7 akhiri dengan *mengklik cell lain yg jauh dari target*
8 Lihat MessageBox


*( B )*
*
*
*VBA Code: Mengcopy range Asal ke Range Tujuan*
yang copy:  Formulanya, FormatNumbers nya dan FORMAT nya

   ' Range ASAL di copy
    Range("B1:E11").Copy

   ' Range Tujuan di PASTE SPECIAL: formula, & *NumberFormats*
    Range("B10:E10").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

   ' Range Tujuan di PASTE SPECIAL:* Formats*
    Range("B10:E10").PasteSpecial Paste:=xlPasteFormats
   ' menghilangkan garis las vegas (range yg masih diingat di clip board
memory)
  Application.CutCopyMode = xlCopy

Jika kemudian hasilnya di copy ke dirinya sendiri (VALUES nya saja)
pengcopyan format numbers dan FORMAT tidak perlu diulang
karena tidak akan terhapus oleh copy VALUES only.

Mohon di coba coba lagi, barangkali masih ada hal yg belum pass benar..

wassalamualaikum wr wb
-iHaps-



2011/12/19 ghozi alkatiri <[hidden email]>

> **
> terima kasih mbak haps.
> akhirnya dengan modifikasi pada beberapa baris kode dengan referensi yang
> sudah diberikan mbak haps dan beberpa kawan lainnya saya berhasil
> mendapatkan yang saya harapkan.
> untuk koreksi di bawah ini saya tuliskan  code dari mbak haps dan
> modifikasi yang saya lakukan.
>
> kode dari mbak haps
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>    ' iHaps // 19 dec , 2001
>    ' CARA 2  : copy paste cells/formula
>    '-----------------------------------
>    Dim ContohRumus As Range
>    Set ContohRumus = Range("B1:D1")
>    If Target.Count = 1 Then
>       If Target.Column = 1 Then
>          If Target.Row > 9 Then
>             If Not Target.Value = vbNullString Then
>                ContohRumus.Copy
>                Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
>                Application.CutCopyMode = False
>                ActiveCell.Offset(1, -1).Range("A1").Select  ' =(bukan
> dari haps)
>             Else
>                Target(1, 2).Resize(1, 3).ClearContents
>             End If
>          End If
>       End If
>    End If
> End Sub
>
> modifikasi Ghozi
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>    ' iHaps // 19 dec , 2001
>    ' CARA 2  : copy paste cells/formula
>    '-----------------------------------
>    Dim formula As Range
>    Set formula = Range("B1:D1")
>    If Target.Count = 1 Then
>       If Target.Column = 1 Then
>          If Target.Row > 9 Then
>             If Not Target.Value = vbNullString Then
>                formula.Copy
>                Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
>                Application.CutCopyMode = False
>                *ActiveCell.*Offset(1, -1).Range("A1").Select
>             Else
>                Target(1, 2).Resize(1, 3).ClearContents
>             End If
>          End If
>       End If
>    End If
> End Sub
>
> hasil dari modifikasi ini sangat pas dengan yang saya inginkan. tetapi
> karena saya masih sangat awam sekali tentang macro saya ingin mendapatkan
> koreksi dari mbak haps dan teman teman lainnya  apa kode hasil modifikasi
> di atas ini sudah aman untuk digunakan tanpa efek samping saya tunggu
> tanggapannya.
>
> *pertanyaan tambahan *
> bagaimana kalau dalam set hasil copy juga di pastekan format dari range
> asal
> jadi yang akan tercopy di samping nilai hasil formula juga format dari
> range asal misalkan border warna dll.
> ini efisiensi supaya tidak menggunakan conditional formating.
>
> wassalam
>
> Ghozi Alkatiri
>
> *Dari:* i Haps <[hidden email]>
> *Dikirim:* Senin, 19 Desember 2011 15:48
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    ' iHaps // 19 dec , 2001
>    ' assignment hasil expressi ke suatu cell/range
>    '---------------------------------------------
>    If Target.Count = 1 Then
>       If Target.Column = 1 Then
>          If Target.Row > 9 Then
>             If Not Target.Value = vbNullString Then
>                Range(Target(1, 2), Target(1, 4)).NumberFormat = "@"
>                Target(1, 2) = Mid(Target.Value, 1, 2)
>                Target(1, 3) = Mid(Target.Value, 3, 2)
>                Target(1, 4) = Mid(Target.Value, 5, 2)
>                Application.CutCopyMode = False
>             Else
>                *Range(Target(1, 2), Target(1, 4)).ClearContents*
>             End If
>          End If
>       End If
>    End If
> End Sub
>
> '--------------
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    ' iHaps // 19 dec , 2001
>    ' CARA 2  : copy paste cells/formula
>    '-----------------------------------
>    If Target.Count = 1 Then
>       If Target.Column = 1 Then
>          If Target.Row > 9 Then
>             If Not Target.Value = vbNullString Then
>                *Range("B1:D1")*.Copy
>                Target(1, 2).PasteSpecial xlPaste*Formulas*AndNumberFormats
>                Target(1, 2).Resize(1, 3).Copy
>                Target(1, 2).Resize(1, 3).PasteSpecial xlPaste*Values*
>             Else
>                *Target(1, 2).Resize(1, 3).ClearContents*
>             End If
>          End If
>       End If
>    End If
> End Sub
> '------------------
>
> Demikain Pak, mudah-mudahan menjadi semakin jelas.
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
sekali lagi terima kasih atas pencerahan dari mbak haps yang demikian detail, jauh lebih mudah saya pahami dari pada kebanyakan buku buku referensi yang ada di pasaran.  Alkhamdulillah sebagian sudah saya terapkan dan hasilnya oke semuanya. 

wassalam

Ghozi Alkatiri


________________________________
 Dari: i Haps <[hidden email]>
Kepada: [hidden email]
Dikirim: Senin, 19 Desember 2011 18:35
Judul: Re: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 

 
Ysh pak Ghozi,
( A )

Ada hal yg perlu mendapat perhatian; yaitu object Range : TARGET dan ActiveCell
Keduanya (dalam Event Sheet_Change)  punya address yg berbeda, bisa berbeda jauh & 
tidak tertentu perbandingan jraknya

Jika target adalah sudah FIXED yaitu cell yg berubah 
Misalnya cell B4 di ubah dari kosong menjadi berisi data "X"
maka yg disebut sebagai TARGET (objet Range) adalah cell B4

Sedangkan ActiveCelladalah cell dimana cellpointer berda ketika terjadi perubahan.
(perubahan dianggap terjadi bila cell sudah diENTER atau cell pointer telah meninggalkan cell target.)
Hal ini sering lupt dari perhatian penulis VBA Excel di tahap-tahap awal.

Kita ketahui bahwa di [ Excel Option ] bisa diset  gerakan cell ketika kita mengenter pengisian cell
bisa  = DOWN / ke CELL DI BAWAHnya  bisa RIGHT / ke CELL DI KANANNYA
Tentunya emakai makro kita tidak bisa kita paksa untuk menyeragamkan setting tsb.

Selain itu, Malah kita bisamenyudahi penulisan di cell dengan mengklik CELL LAIN mana saja
yang mungkin JAUH sekali dari cell TARGET dan tempatnya pun tidak terduga !!

Jika sudah kita ketahui seperti itu keadaannya, maka pemakaiam Object ActiveCell
rasanya tidak aman lagi, karena "beada di mana-kah ActiveCell saat itu",  kita tidak bisa menduganya.

Maka itu di kedua Prosedur yg Haps kirimkan tadi,  tidak ada keyword Activecell, yang ada hanya Target
yaitu demi amannya (yakinnya) letak dan Dimensi Range yg akan diolah/proses,

Modifikasi dari pak Ghozi
  ActiveCell.Offset(1, -1).Range("A1").Select
jika maksudnya menSelect INPUT cell berikutnya(cell yg berada di bawah target yg baru saja
selesai diisi);  mungkin bisa diganti dengan kode seperti ini
  Target(2,1).Select
(ingat bahwa object Range Target sendiri (jika ia berupa cell tunggal) bisa disebut dengan Target(1,1)
Dua buah parameter itu adalah index untuk baris dan kolom dilihat dari titik pusatnya = Target)

Terlampir makro yg membuktikan bahwa Aktivecell bisa tidak FIXED jaraknya jika dilihat dari TARGET
karena 3 (tiga) alasan tadi (bisa di kanannya, bisa dibawahnya, dan kalau user mengakhiri pengisian cell
dengan mengklik cell lain mana saja, maka ActiveCell bisa di mana saja)

----ditulis di Module Sheet----
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count = 1 Then
      MsgBox "TARGET  = " & Target.Address & vbCrLf & _
           "CellAktif = " & ActiveCell.Address
   End If
End Sub

adakan percobaan:
1. mengedit cell B4 / mengisi cell B4 dengan data baru
2lalu di enter
3. lihat MsgBox

4 ubah setting diexcel option (mengenai arah berpindahnya cell ketika dienter (Down / Right)
5 ulangi 1 s/d 3
6 ulangi 1 
7 akhiri dengan mengklik cell lain yg jauh dari target
8 Lihat MessageBox


( B )

VBA Code: Mengcopy range Asal ke Range Tujuan
yang copy:  Formulanya, FormatNumbers nya dan FORMAT nya

   ' Range ASAL di copy
    Range("B1:E11").Copy

   'Range Tujuan di PASTE SPECIAL: formula, & NumberFormats
    Range("B10:E10").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

   ' Range Tujuan di PASTE SPECIAL:Formats 
    Range("B10:E10").PasteSpecial Paste:=xlPasteFormats
   ' menghilangkan garis las vegas (range yg masih diingat di clip board memory) 
  Application.CutCopyMode = xlCopy

Jika kemudian hasilnya di copy ke dirinya sendiri (VALUES nya saja)
pengcopyan format numbers dan FORMAT tidak perlu diulang
karena tidak akan terhapus oleh copy VALUES only.

Mohon di coba coba lagi, barangkali masih ada hal yg belum pass benar..

wassalamualaikum wr wb
-iHaps-



2011/12/19 ghozi alkatiri <[hidden email]>

 

>terima kasih mbak haps.
>akhirnya dengan modifikasi pada beberapa baris kode dengan referensi yang sudah diberikan mbak haps dan beberpa kawan lainnya saya berhasil mendapatkan yang saya harapkan.
>untuk koreksi di bawah ini saya tuliskan  code dari mbak haps dan modifikasi yang saya lakukan.
>
>
>kode dari mbak haps
>
>
>Option Explicit
>Private Sub Worksheet_Change(ByVal Target As Range)
>   ' iHaps // 19 dec , 2001
>   ' CARA 2  : copy paste cells/formula
>   '-----------------------------------
>   Dim ContohRumus As Range
>   Set ContohRumus = Range("B1:D1")
>   If Target.Count = 1 Then
>      If Target.Column = 1 Then
>         If Target.Row > 9 Then
>            If Not Target.Value = vbNullString Then
>               ContohRumus.Copy
>               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
>               Application.CutCopyMode = False
>               ActiveCell.Offset(1, -1).Range("A1").Select  ' =(bukan dari haps)
>            Else
>               Target(1, 2).Resize(1, 3).ClearContents
>            End If
>         End If
>      End If
>   End If
>End Sub
>
>
>modifikasi Ghozi
>Option Explicit
>Private Sub Worksheet_Change(ByVal Target As Range)
>   ' iHaps // 19 dec , 2001
>   ' CARA 2  : copy paste cells/formula
>   '-----------------------------------
>   Dim formula As Range
>   Set formula = Range("B1:D1")
>   If Target.Count = 1 Then
>      If Target.Column = 1 Then
>         If Target.Row > 9 Then
>            If Not Target.Value = vbNullString Then
>               formula.Copy
>               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
>               Application.CutCopyMode = False
>               ActiveCell.Offset(1, -1).Range("A1").Select
>            Else
>               Target(1, 2).Resize(1, 3).ClearContents
>            End If
>         End If
>      End If
>   End If
>End Sub
>
>
>hasil dari modifikasi ini sangat pas dengan yang saya inginkan. tetapi karena saya masih sangat awam sekali tentang macro saya ingin mendapatkan koreksi dari mbak haps dan teman teman lainnya  apa kode hasil modifikasi di atas ini sudah aman untuk digunakan tanpa efek samping saya tunggu tanggapannya.
>
>
>pertanyaan tambahan 
>bagaimana kalau dalam set hasil copy juga di pastekan format dari range asal 
>jadi yang akan tercopy di samping nilai hasil formula juga format dari range asal misalkan border warna dll.
>ini efisiensi supaya tidak menggunakan conditional formating.
>
>
>wassalam
>
>
>Ghozi Alkatiri
>
>
>Dari: i Haps <[hidden email]>
>Dikirim: Senin, 19 Desember 2011 15:48
>

>
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>   ' iHaps // 19 dec , 2001
>   ' assignment hasil expressi ke suatu cell/range
>   '---------------------------------------------
>   If Target.Count = 1 Then
>      If Target.Column = 1 Then
>         If Target.Row > 9 Then
>            If Not Target.Value = vbNullString Then
>               Range(Target(1, 2), Target(1, 4)).NumberFormat = "@"
>               Target(1, 2) = Mid(Target.Value, 1, 2)
>               Target(1, 3) = Mid(Target.Value, 3, 2)
>               Target(1, 4) = Mid(Target.Value, 5, 2)
>               Application.CutCopyMode = False
>            Else
>               Range(Target(1, 2), Target(1, 4)).ClearContents
>            End If
>         End If
>      End If
>   End If
>End Sub
>
>
>'--------------
>
>
>
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>   ' iHaps // 19 dec , 2001
>   ' CARA 2  : copy paste cells/formula
>   '-----------------------------------
>   If Target.Count = 1 Then
>      If Target.Column = 1 Then
>         If Target.Row > 9 Then
>            If Not Target.Value = vbNullString Then
>               Range("B1:D1").Copy
>               Target(1, 2).PasteSpecial xlPasteFormulasAndNumberFormats
>               Target(1, 2).Resize(1, 3).Copy
>               Target(1, 2).Resize(1, 3).PasteSpecial xlPasteValues
>            Else
>               Target(1, 2).Resize(1, 3).ClearContents
>            End If
>         End If
>      End If
>   End If
>End Sub
>'------------------
>
>
>Demikain Pak, mudah-mudahan menjadi semakin jelas.
>
>
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

Edy Wiyono
In reply to this post by ghozi alkatiri
Mas Gozy.

 

Coba lihat lagi formula di file sampeyan.

B1=MID($A1,1,2)

C1=MID(A$1,3,2)

D1 =MID(A$1,5,2)

 

Perbedaan antara B1 dan C1 dan D1 adalah pada tanda $ (dolarnya).

 

Coba ganti dengan  sbb:

 

C1 dengan MID($A1,3,2)

D1 dengan MID($A1,5,2)

 

Dan hasilnya akan seperti yang sampeyan harapkan.

 

Salam hangat dan jabat erat,

 

Edy Wiyono

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of ghozi alkatiri
Sent: Sunday, December 18, 2011 10:52 PM
To: [hidden email]
Subject: Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

 

 

terima kasih atas tanggapannya.

sebenanrnya solusi yang bapak kirimkan hampir mendekati yang saya inginkan tapi rupanya ada kesalahan teknis pada hasil di kolom ke dua dan ke tiga dari hasil yang  diharapkan.

misalkan saya menginput data 123456 di kolom A hasil yang saya inginkan adalah kopi formula dari range rujukan

seharusnya menghasilkan nilai di kolom B 12 di kolom C 34 dan di kolom D 56 tapi pada  kenyataanya yang benar hanya hasil pada kolom B saja. sedangkan di kolom B dan C nilai apapun yang dimasukkan hasilnya selalu  cd dan ef.  

kalau tidak merepotkan saya tunggu koreksinya.

sekali lagi terima kasih.

 

wassalam

 

  _____  

Dari: Edy Wiyono <[hidden email]>
Kepada: [hidden email]
Dikirim: Minggu, 18 Desember 2011 17:51
Judul: RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

 

Waalaikumsalam, Pak Ghozi
Saya menggunakan vba untuk file contohnya.
Mudah-mudahan bisa membantu.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("sheet1").Activate Then
If ActiveCell.Column = 1 Then
If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
a = ActiveCell.Row
b = ActiveCell.Column

If a = Cells(1, 100) And b = Cells(1, 101) Then
Exit Sub
End If

Range("formula").Select
Selection.Copy
Cells(a - 1, b + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Cells(1, 100) = a
Cells(1, 101) = b
Cells(a, b).Select
Application.CutCopyMode = False
ActiveWorkbook.Save

End If
End If
End If
End Sub

From: [hidden email] <mailto:belajar-excel%40yahoogroups.com>  
On Behalf Of ghozi alkatiri
Sent: Saturday, December 17, 2011 8:34 PM

terima kasih atas tanggapan dari mr kid dan mbak haps, semuanya akan saya simpan sebagai referensi dan akan saya pelajari lebih lanjut.

khusus mengenai tanggapan dari mbak haps sudah mendekati yang saya inginkan .

cuma yang saya inginkan adalah proses satu demi satu tidak secara keseluruhan seperti yang dicontohkan mbak haps.

jelasnya begini

misalkan saya menginput nilai ABCDEF di A10 maka ketika saya enter akan terjadi proses pengcopyan dari range formula yang sudah disiapkan. dan meninggalkan nilai valuenya saja di AB,CD dan EF di cell C10, D10 dan E10

demikian juga ketika saya lanjutkan dengan baris berikutnya. proses pengcopian akan terjadi sesaat setelah saya menekan enter. tanpa harus menekan run macro atau klik tombol.

wassalam

Ghozi Alkatiri

_____

Dari: Haps <[hidden email] <mailto:hapsari.stlizbeth%40gmail.com> >
Dikirim: Sabtu, 17 Desember 2011 20:17
Sub Cara_Satu()
Sub Cara_Dua()

 



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input

ghozi alkatiri
terima kasih atas koreksinya . 

wassalam 

Ghozi Alkatiri


________________________________
 Dari: Edy Wiyono <[hidden email]>
Kepada: [hidden email]
Dikirim: Selasa, 20 Desember 2011 21:49
Judul: RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 

 
Mas Gozy.
 
Coba lihat lagi formula di file sampeyan.
B1=MID($A1,1,2)
C1=MID(A$1,3,2)
D1 =MID(A$1,5,2)
 
Perbedaan antara B1 dan C1 dan D1 adalah pada tanda $ (dolarnya).
 
Coba ganti dengan  sbb:
 
C1 dengan MID($A1,3,2)
D1 dengan MID($A1,5,2)
 
Dan hasilnya akan seperti yang sampeyan harapkan.
 
Salam hangat dan jabat erat,
 
Edy Wiyono
 
From:[hidden email] [mailto:[hidden email]] On Behalf Of ghozi alkatiri
Sent: Sunday, December 18, 2011 10:52 PM
To: [hidden email]
Subject: Bls: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 
 
terima kasih atas tanggapannya. 
sebenanrnya solusi yang bapak kirimkan hampir mendekati yang saya inginkan tapi rupanya ada kesalahan teknis pada hasil di kolom ke dua dan ke tiga dari hasil yang  diharapkan.
misalkan saya menginput data 123456 di kolom A hasil yang saya inginkan adalah kopi formula dari range rujukan
seharusnya menghasilkan nilai di kolom B 12 di kolom C 34 dan di kolom D 56 tapi pada  kenyataanya yang benar hanya hasil pada kolom B saja. sedangkan di kolom B dan C nilai apapun yang dimasukkan hasilnya selalu  cd dan ef.  
kalau tidak merepotkan saya tunggu koreksinya.
sekali lagi terima kasih.
 
wassalam
 

________________________________

Dari:Edy Wiyono <[hidden email]>
Kepada: [hidden email]
Dikirim: Minggu, 18 Desember 2011 17:51
Judul: RE: [belajar-excel] kode macro untuk mengcopy range berfomula ke kolom di kanan cell input
 
Waalaikumsalam, Pak Ghozi
Saya menggunakan vba untuk file contohnya.
Mudah-mudahan bisa membantu.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("sheet1").Activate Then
If ActiveCell.Column = 1 Then
If Len(Cells((ActiveCell.Row - 1), ActiveCell.Column)) >= 6 Then
a = ActiveCell.Row
b = ActiveCell.Column

If a = Cells(1, 100) And b = Cells(1, 101) Then
Exit Sub
End If

Range("formula").Select
Selection.Copy
Cells(a - 1, b + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Cells(1, 100) = a
Cells(1, 101) = b
Cells(a, b).Select
Application.CutCopyMode = False
ActiveWorkbook.Save

End If
End If
End If
End Sub

From: [hidden email]
On Behalf Of ghozi alkatiri
Sent: Saturday, December 17, 2011 8:34 PM

terima kasih atas tanggapan dari mr kid dan mbak haps, semuanya akan saya simpan sebagai referensi dan akan saya pelajari lebih lanjut.

khusus mengenai tanggapan dari mbak haps sudah mendekati yang saya inginkan .

cuma yang saya inginkan adalah proses satu demi satu tidak secara keseluruhan seperti yang dicontohkan mbak haps.

jelasnya begini

misalkan saya menginput nilai ABCDEF di A10 maka ketika saya enter akan terjadi proses pengcopyan dari range formula yang sudah disiapkan. dan meninggalkan nilai valuenya saja di AB,CD dan EF di cell C10, D10 dan E10

demikian juga ketika saya lanjutkan dengan baris berikutnya. proses pengcopian akan terjadi sesaat setelah saya menekan enter. tanpa harus menekan run macro atau klik tombol.

wassalam

Ghozi Alkatiri

_____

Dari: Haps <[hidden email]>
Dikirim: Sabtu, 17 Desember 2011 20:17
Sub Cara_Satu()
Sub Cara_Dua()
 
 
Loading...