Re: ]] XL-mania [[ run time error 16 expression too complex in excel 2007

 

Saya jarang menggunakan makro (hanya sebatas record), tapi sepertinya kalau kita lihat hasil dari macro record, di 2007 harus sebutkan nama sheet untuk defined name. Mungkin karena di 2007, nama bisa cuma berlaku di salah satu sheet saja.

 

Jadi coba: ganti Range("akhir").Value menjadi misalnya:

 

sheets("nama_sheet").range("akhir").value

atau

sheets(index_sheet).range("akhir").value

atau

activesheet.range("akhir").value  '----(dengan sebelumnya memilih sheet tempat range "akhir" berada)

 

 

blessings,

tomz

 

----- Original Message -----

From: bagus nuradita

To: XL-mania@yahoogroups.com

Sent: Saturday, February 04, 2012 3:59 PM

Subject: ]] XL-mania [[ run time error 16 expression too complex in excel 2007

 

Dear all. saya punya file excel dng macro vbnya tiba2 muncul  error  "run time error 16 expression too complex " begitu di upgrade ke exel 2007. file ini berjalan dgn normal di excel 2003 Sub isiupah()    Dim i As Double     Dim j As Double     Dim nom As Double     Dim BAR As Double     Dim NOPEK, NOURUT, NIK1, SEKS  As String     Dim NAMA As String     Dim LAHIR As String     Dim UPAH As Double     Dim bln As String     Dim kol As Double     Sheets("upah").Activate     If Range("JUM1cku").Value > 2 Then         Do While Range("JUM1cku").Value > 2            Range("PER1c").Select            ActiveCell.Offset(-1, 0).Range("A1").Select            Selection.EntireRow.Delete         Loop     End If     NOURUT = 0     For i = 1 To Range("akhir").Value  => run time error 16 expression too complex         For j = 1 To 23             bln = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAku"), 7 + j, False)         If j = 1 Then         If Sheets("dumtk").Range("a5").Offset(i, j + 6).Range("a1").Value <> Sheets("dumtk").Range("a5").Offset(i, 31).Range("a1").Value Then                       If Val(Sheets("dumtk").Range("a5").Offset(i, 31).Range("a1").Value) <> 0 Then            If Val(Sheets("dumtk").Range("a5").Offset(i, j + 6).Range("a1").Value) <> 0 Then            nom = ((j - 1) / 2) + 1            If nom = Val(Range("kuupah").Value) Then            NOURUT = NOURUT + 1            NIK1 = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 3, False)            SEKS = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 6, False)            NOPEK = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 2, False)            NAMA = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 4, False)            kol = 7 + j            LAHIR = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 5, False)            If WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAku"), kol, False) <> "K" Then                UPAH = Val(WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAku"), kol, False))                       Range("NOMORUPAH").Select            Selection.EntireRow.Insert             ActiveCell.FormulaR1C1 = NOURUT             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NIK1             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NOPEK             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NAMA             ActiveCell.Offset(0, 3).Range("A1").Select             ActiveCell.FormulaR1C1 = LAHIR             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = SEKS             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = UPAH            End If            End If            End If            End If         End If         Else         If Sheets("dumtk").Range("a5").Offset(i, j + 6).Range("a1").Value <> Sheets("dumtk").Range("a5").Offset(i, j + 4).Range("a1").Value Then                       If Val(Sheets("dumtk").Range("a5").Offset(i, j + 4).Range("a1").Value) <> 0 Then            If Val(Sheets("dumtk").Range("a5").Offset(i, j + 6).Range("a1").Value) <> 0 Then            nom = ((j - 1) / 2) + 1            If nom = Val(Range("kuupah").Value) Then            NOURUT = NOURUT + 1            NIK1 = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 3, False)            SEKS = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 6, False)            NOPEK = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 2, False)            NAMA = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 4, False)            kol = 7 + j            LAHIR = WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAKU"), 5, False)            If WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAku"), kol, False) <> "K" Then                UPAH = Val(WorksheetFunction.VLookup(i, Sheets("dumtk").Range("DATAku"), kol, False))                       Range("NOMORUPAH").Select            Selection.EntireRow.Insert             ActiveCell.FormulaR1C1 = NOURUT             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NIK1             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NOPEK             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = NAMA             ActiveCell.Offset(0, 3).Range("A1").Select             ActiveCell.FormulaR1C1 = LAHIR             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = SEKS             ActiveCell.Offset(0, 1).Range("A1").Select             ActiveCell.FormulaR1C1 = UPAH            End If            End If            End If            End If         End If         End If         Next j     Next i End Sub Mohon sarannya

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 83

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Delay pada posting disebabkan sibuknya momods dan leletnya        |
| koneksi dengan ind***t. bwakakakaka....
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message,  melecehkan,  tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan  winzip  jika  perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke  |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau...  |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id]  |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___