ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¥X²{RUN-TIME ERROR '9': SUBSCRIPT OUT OF RANGE,½Ð°Ý­þ¸Ì¥X²{°ÝÃD¡H

¦^´_ 39# 198188
    ¤G­Ódata base¡A´NÅܦ¨ªþ¥ó¹Ï¤ù
FÄæ­n³]©w¬°¤é´Á®æ¦¡ ¦p¹Ï

EX1.GIF
2013-1-11 16:06

TOP

¦^´_ 41# GBKEE


    §Ú¸Õ¹L±N¥þ³¡ªº¤é´Á®æ¦¡³£§ï¦¨¤@¼Ë¡A¦Ó¦UºØ¤é´Á®æ¦¡³£¸Õ¹L¡C
¦ý¤@°õ¦ævba´N·|¦Û°Ê±N®æ¦¡§ï¦¨¼Æ¦r¦p¤U¡A¦Ó§Ú·Q±N³o­ÓETA¤é´Á´î¨â¤é¡A¦pªG¬O¬P´Á¤»©Î¤é¡A´N¦b¥t¤@columnÅã¥ÜETA Columnªº¤é´Á¡A§_«h´NÅã¥ÜETA¤é´Á´î¨â¤é,¥Ñ©óvba¥¼¯à¿ë»{¼Æ¦r¦¨¤é´Á¡A©Ò¥H¥þ³¡³£´î2
ETA
41289.5
41291
41288.5
41295
41297
41298

TOP

¦^´_ 42# 198188
¤W¶ÇÀɮפε{¦¡½X¬Ý¬Ý.

TOP

¥»©«³Ì«á¥Ñ 198188 ©ó 2013-1-13 00:46 ½s¿è

¦^´_ 43# GBKEE
1.rar (380.46 KB)
2.rar (464.33 KB)
3.rar (419.49 KB)

vba.rar (2.65 KB)

¥Ñ©óÀɮפӤj¡A§Ú¤À¶}¤F7­Ó excel ©M¤@­Óµ{¦¡ªºªþ¥ó

4.rar (903.92 KB)

5.rar (760.94 KB)

4.rar (903.92 KB)

5.rar (760.94 KB)

TOP

¦^´_ 44# 198188
¸Õ¸Õ¬Ý
  1. '6¡^
  2. Sub Request()
  3.     Dim rngSrc As Range, rngCopyField As Range, rngFilter As Range
  4.     Dim nextRow As Long, endRow As Long
  5.     Dim LastRec As Integer
  6.     Dim i As Integer
  7.     Dim The_day As String  '<- ****
  8.     Sheets("Request").[A2:AG65536].ClearContents
  9.     Set rngSrc = Sheets("State").[A1:AG65536]
  10.     Set rngCopyField = Sheets("Rule").[B21:AH21]
  11.     Set rngFilter = Sheets("Rule").[B14].Resize(Sheets("Rule").[B14].CurrentRegion.Rows.Count, 33)
  12.      nextRow = 2
  13.     Sheets("Request").UsedRange.Offset(1).Clear
  14.     rngSrc.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
  15.     rngFilter, CopyToRange:=Sheets("Request").Range("A" & nextRow)
  16.          endRow = Sheets("Request").Range("A" & Sheets("Request").Rows.Count).End(xlUp)
  17.         For i = 1 To rngCopyField.Count
  18.         If rngCopyField(i) = "N" Then
  19.             Sheets("Request").Range(nextRow & ":" & endRow).Columns(i).Clear
  20.         End If
  21.     Next
  22.         Sheets("Request").Range("A" & nextRow).Resize(1, 33).Delete Shift:=xlUp   'delete header
  23.        Set rngSrc = Nothing
  24.     Set rngCopyField = Nothing
  25.     Set rngFilter = Nothing
  26. With Worksheets("Request")
  27.    LastRec = .Range("A1").End(xlDown).Row
  28.   For i = 2 To LastRec
  29.     .Range("B" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:S"), 19, False)
  30.     .Range("C" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:AA"), 27, False)
  31.     .Range("D" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:U"), 21, False) & " - " & Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:AC"), 29, False)
  32.     .Range("E" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:AB"), 28, False)
  33.     .Range("F" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:B"), 2, False)
  34.     .Range("G" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("State").Range("A:N"), 14, False)
  35.     The_day = Format(Worksheets("Request").Range("F" & i).Value - 2, "AAA")  '¶Ç¦^¬P´Á?
  36.     If The_day = "¬P´Á¤»" Or The_day = "¬P´Á¤é" Then
  37.         .Range("H" & i).Value = .Range("F" & i).Value
  38.     Else
  39.         .Range("H" & i).Text = .Range("F" & i).Value - 2
  40.     End If
  41.     .Range("F" & i).NumberFormatLocal = "m/d;@"   '³]©w¤é´Á®æ¦¡
  42.     .Range("H" & i).NumberFormatLocal = "m/d;@"   '³]©w¤é´Á®æ¦¡
  43.       Next
  44.     End With
  45. End Sub
½Æ»s¥N½X

TOP

¦^´_ 45# GBKEE


     .Range("H" & i).Text = .Range("F" & i).Value - 2
°õ¦æ¶¥¬q¿ù»~¡¥1004¡¦
µLªk³]©wºØÃþrangeªºtext ÄÝ©Ê

TOP

¦^´_ 46# 198188
§ï¦¨
  1. .Range("H" & i).Value
½Æ»s¥N½X

TOP

¦^´_ 47# GBKEE


   
¥¼©R¦W.png
2013-1-14 19:33


¹B¦æ¥X¨Óªºµ²ªGÁÙ¬O¤@¼Ë¡A¤£À´±o¿ë»{¬P´Á¤»©ÎªÌ¬P´Á¤é

TOP

¦^´_ 48# 198188
Debug.Print¥i¦b §Y®É¹Bºâµøµ¡  ¤¤¬d¬Ý  The_day ¶Ç¦^ªº¦r¦ê
   
  1. The_day = Format(Worksheets("Request").Range("F" & i).Value - 2, "AAA")  '¶Ç¦^¬P´Á?
  2.     Debug.Print The_day   ' *** Debug.Print¥i¦b §Y®É¹Bºâµøµ¡  ¤¤¬d¬Ý  The_day ¶Ç¦^ªº¦r¦ê
  3.     If The_day = "¬P´Á¤»" Or The_day = "¬P´Á¤é" Then
½Æ»s¥N½X



EX1.GIF
2013-1-15 16:38

TOP

¦^´_ 49# GBKEE


  ·PÁ¡C¤w¸gª¾¹D°ÝÃD©Ò¦b¡A§Úªº¹q¸£¬O¶g¤»©ÎªÌ¶g¤é¡A¤£¬O¬P´Á¤»©ÎªÌ¬P´Á¤é

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD