[µo°Ý] ½Ð±Ð§ï¨}³t«×¤èªk

stillfish00

¦^´_ 19# 198188
¥i¥H¦Û¤v§ïªü , §Ú¬O¥H¬°§A­n¤@ª½·s¼W¤~³o¼Ë¼gªº..
    'nextRow = Sheets("Result").UsedRange.Rows.Count + 1
    nextRow = 2
    Sheets("Result").UsedRange.Offset(1).Clear
    ---²¤---
    'endRow = Sheets("Result").UsedRange.Rows.Count
    endRow = Sheets("Result").Range("A" & Sheets("Result").Rows.Count).End(xlUp)

198188

Sub Worksheet()
Dim LastRec As Integer
Dim i As Integer

Worksheets("Oracle").Range("G1").Select
ActiveCell.End(xlDown).Select
     LastRec = ActiveCell.Row
     
For i = 2 To LastRec
Worksheets("Oracle").Range("A" & i).Value = Worksheets("Oracle").Range("G" & i).Value '±NGÄ檺­ÈCOPY¨ìAÄæ

If IsError(Application.VLookup(Worksheets("Oracle").Range("C" & i).Value, Sheets("Follower").Range("A:E"), 5, False)) Then
Worksheets("Oracle").Range("B" & i).Value = ""  °²¦pSHEET ORACLE CÄ檺­È¦bSHEET FOLOWER A¡GEÄæ§ä¤£¨ì¡A´N±NBÄæ³]©wªÅ®æ
Else
Worksheets("Oracle").Range("B" & i).Value = Application.VLookup(Worksheets("Oracle").Range("c" & i).Value, Sheets("Follower").Range("A:E"), 5, False) §_«h±NSHEET ORACLE CÄæ¦bSHEET FOLLOWER´M§ä¬Û¦P¡A¨Ã±N¬Û¦PªºEÄæ­È©ñ¦bSHEET ORACLE BÄæ
End If

Next i
i = i + 1

End Sub

¹B¦æ¤j¬ù7000ROW,¦ý¹B¦æ±o«ÜºC¡A½Ð°Ý¦U¦ì¤j¤j¡A¦³¨S¦³¥i¥H¥[§Ö¹B¦æªº¤èªk¡H

GBKEE

¦^´_ 22# 198188
´ú¸Õ§¹¦¨¬ù12¬í,®É¶¡¤£ºâªø§a!

1. Option Explicit
   
2. Sub Worksheet()
   
3.     Dim LastRec As Integer
   
4.     Dim i As Integer, T
   
5.     T = Time
   
6.     With Worksheets("Oracle")
   
7.          LastRec = .Range("G1").End(xlDown).Row
   
8.         .Range("A2:A" & LastRec).Value = .Range("G2:G" & LastRec).Value  '¥þ³¡ª½±µµ¹­È·|§Ö¨Ç
   
9.         For i = 2 To LastRec
   
10.             If IsError(Application.VLookup(Worksheets("Oracle").Range("C" & i).Value, Sheets("Follower").Range("A:E"), 5, False)) Then
    
11.                 .Range("B" & i).Value = ""
    
12.             Else
    
13.                 .Range("B" & i).Value = Application.VLookup(Worksheets("Oracle").Range("c" & i).Value, Sheets("Follower").Range("A:E"), 5, False)
    
14.             End If
    
15.         Next
    
16.     End With
    
17.     MsgBox Format(Time - T, "HH:MM:SS")
    
18. End Sub

½Æ»s¥N½X

198188

¦^´_ 23# GBKEE

12¬í°÷§Ö¤F¡AÁÂÁÂ~
¥t¥~½Ð°Ý¦pªG¥ÎOption Explicit¡A§Ú±N¸ê®Æ±q¥t¥~¤@­Óexcel copy¹L¨Ó¡A¥¦·|¦Û°Êrun¶Ü¡HÁÙ¬O»Ý­n¥t¥~«ö¶s¤~¯àrun?

GBKEE

¦^´_ 24# 198188
Option Explicit ³¯­z¦¡ ¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C
¼Ò²Õ¼h¦³Option Explicit§@¥Î¬O:  ¨t²Î·|´£¿ô,¨S«Å§iªºÅܼƶ·«Å§i. ­n¾i¦¨³o²ßºD ¦³§Uµ{¦¡ªº°»¿ù.

198188

¦^´_ 25# GBKEE


    ·PÁ¸ÑÄÀ~
½Ð°Ý¦pªG­ncopy¹L¥h«á¡A¤£«ö¶s´N¦Û°Êrun ¥i¥H¶Ü¡H

GBKEE

¦^´_ 26# 198188
§A­n¦b¦óºØª¬ºA¤U¤£«ö¶s´N¦Û°Êrun
1.Àɮ׶}±Ò®É
A: ¦bThisWorkbook³o¼Ò²Õ¤¤¦³¤@¹w³]ªºµ{§Ç

1. Private Sub Workbook_Open()
   
2.    '³o¸Ìªºµ{¦¡½X¤£«ö¶s´N¦Û°Êrun
   
3. End Sub

½Æ»s¥N½X

B: ¦bVBA ªº¤@¯ë¼Ò²Õ ¼g¤W

1. Sub AUTO_OPEN()  
   
2. '³o¸Ìªºµ{¦¡½X¤£«ö¶s´N¦Û°Êrun
   
3. End Sub

½Æ»s¥N½X

2.Àɮ׶}±Ò«á¿ï©w(²¾°Ê)¨ì³o¤u§@ªí,¦b³o¤u§@ªí¼Ò²Õ¤¤¦³¤@¹w³]ªºµ{§Ç

1. Private Sub Worksheet_Activate()
   
2.    '³o¸Ìªºµ{¦¡½X¤£«ö¶s´N¦Û°Êrun
   
3. End Sub

½Æ»s¥N½X

3.¨Ï¥Î OnTime ¤èªk

1. Sub AUTO_OPEN()  
   
2. Application.OnTime Now + TimeValue("00:05:00"), "¼Ò²Õ.µ{¦¡¦WºÙ"
   
3. End Sub

½Æ»s¥N½X

198188

´N¬O·í¸Ó¸ê®Æ®wªº¸ê®Æ¦³ÅܰʬOrun

198188

¦^´_ 27# GBKEE


   ¦b18ªºµo¨¥
½Ð°ÝÅã¥Ü¤µ¤é©Î¤§«áªº¤é´Á¡AÀ³¸Ó¦p¦ó¶ñ¡H
>=today()   ?
>=5/12/2012 ?

GBKEE

¦^´_ 29# 198188
18# ªþÀÉ [Rule] ¤u§@ªí  Ordered Date Äæ¦ìªº¶i¶¥¿z¿ï¤½¦¡
=">="&DATEVALUE("2012/10/1")    «ü©w¤é´Á
=">="&TODAY()                                    ·í¤é