麻辣家族討論版版 - Powered by Discuz! Board

標題: TEST與資料檔(材料)工作表比對 [打印本頁]

作者: plyl 時間: 2011-3-21 10:28 標題: TEST與資料檔(材料)工作表比對

本帖最後由 plyl 於 2011-3-21 10:39 編輯

Private Sub AA_Click()
Dim myrow, myrow2 As Integer

For myrow = 4 To 50
If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For
For myrow2 = 2 To 5000
   If Sheets("資料檔").Cells(myrow2, 1) = "" Then Exit For
   If Sheets("資料檔").Cells(myrow2, 1) = Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔").Cells(myrow2, 18) Then _
   Sheets("TEST").Cells(myrow, 2) = Sheets("資料檔").Cells(myrow2, 18):
Exit For

Next
Next
End Sub
註:1.我有兩個工作表"TEST' "資料檔"
2.TEST("A4:A50")與資料檔("A2:A500")做比對
   3.完全吻合就將資料檔("A3")貼到TEST ("B4")

麻煩大大幫我解析哪裡出錯謝謝感恩
初學者

作者: GBKEE 時間: 2011-3-21 10:39

If Sheets("資料檔(材料)").Cells(myrow2, 1) = Sheets("TEST").Cells(myrow, 1) AND Sheets("資料檔(材料)").Cells(myrow2, 18) Then

作者: plyl 時間: 2011-3-21 11:09

出現型態不符合 (錯誤 13)

初學者

作者: GBKEE 時間: 2011-3-21 14:16

回復 1# plyl
3.完全吻合就將資料檔("A3")貼到TEST ("B4")
這句對嗎?
是否是完全吻合就將 "資料檔"的資料從TEST ("B4")往下貼上

Option Explicit
'註:1.我有兩個工作表"TEST' "資料檔"
'2.TEST("A4:A50")與資料檔("A2:A500")做比對
'3.完全吻合就將資料檔("A3")貼到TEST ("B4")
Private Sub AA_Click()
Dim myrow, myrow2, myrow3 As Integer
myrow3 = 4
For myrow = 4 To 50
For myrow2 = 2 To 5000
If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔").Cells(myrow2, 1) Then
Sheets("TEST").Cells(myrow3, 2) = Sheets("資料檔").Cells(myrow2, 1)
myrow3 = myrow3 + 1
End If
Next
Next
End Sub

複製代碼

作者: plyl 時間: 2011-3-21 15:53

3.完全吻合就將資料檔("A3")貼到TEST ("B4")
是"D3"
myrow3 = myrow3 + 1 "原來重點就在這"
我又在這後面又加一行不然跳不出去
If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For

GBKEE版主辛苦您了!小弟由衷感謝

作者: GBKEE 時間: 2011-3-21 17:28

回復 5# plyl
Private Sub AA_Click()
Dim myrow, myrow2, myrow3 As Integer
myrow3 = 4
For myrow = 4 To 50
   If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For   是加在這裡嗎?
錯了, 假如上式的myrow=10 且  Sheets("TEST").Cells(myrow, 1) = ""  會離開For myrow 的回圈
那  myrow的 11,12,13....... 的Sheets("TEST").Cells(myrow, 1) 就比對不到了
請改成
If Sheets("TEST").Cells(myrow, 1) <> "" Then
      For myrow2 = 2 To 5000
         If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔").Cells(myrow2, 1) Then
                  Sheets("TEST").Cells(myrow3, 2) = Sheets("資料檔").Cells(myrow2, 1)
                  myrow3 = myrow3 + 1
         End If
      Next
End If
Next
End Sub

作者: plyl 時間: 2011-3-21 19:20

本帖最後由 plyl 於 2011-3-21 19:23 編輯

回復 plyl
Private Sub AA_Click()
Dim myrow, myrow2, myrow3 As Integer
myrow3 = 4
F ...
GBKEE 發表於 2011-3-21 17:28

{原來是這樣放
是不是For myrow = 4 To 50 如果20是空格會離開For myrow 的回圈}

Dim myrow, myrow2, myrow3 As Integer

myrow3 = 4

For myrow = 4 To 50

      For myrow2 = 3 To 5000

         If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then
                  'ITO RECIPE
                  Sheets("TEST").Cells(myrow3, 41) = Sheets("資料檔(材料)").Cells(myrow2, 32)
                  'ITES 可RUN 01 OR 02
                  Sheets("TEST").Cells(myrow3, 64) = Sheets("資料檔(材料)").Cells(myrow2, 33)
                  myrow3 = myrow3 + 1
         If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For    {我是放在這裡}
         End If

      Next

Next

GBKEE版主辛苦您了!小弟由衷感謝

作者: GBKEE 時間: 2011-3-22 11:34

回復 7# plyl
If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then
如這條件成立時  請問Sheets("資料檔(材料)").Cells(myrow2, 1)是有資料的嗎?
                  'ITO RECIPE
            Sheets("TEST").Cells(myrow3, 41) = Sheets("資料檔(材料)").Cells(myrow2, 32)
               'ITES 可RUN 01 OR 02
            Sheets("TEST").Cells(myrow3, 64) = Sheets("資料檔(材料)").Cells(myrow2, 33)
                  myrow3 = myrow3 + 1
         If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For    {我是放在這裡}
         如有資料上式永不會成立的
End If

作者: plyl 時間: 2011-3-23 05:59

本帖最後由 plyl 於 2011-3-23 06:01 編輯

If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then

Sheets("資料檔(材料)").Cells(myrow2, 1)  有資料

方法1
Dim myrow, myrow2, myrow3 As Integer

myrow3 = 4

For myrow = 4 To 50
If Sheets("TEST").Cells(myrow, 1) <> "" Then       For myrow2 = 3 To 5000

         If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then
                  'ITO RECIPE
                  Sheets("TEST").Cells(myrow3, 41) = Sheets("資料檔(材料)").Cells(myrow2, 32)
                  'ITES 可RUN 01 OR 02
                  Sheets("TEST").Cells(myrow3, 64) = Sheets("資料檔(材料)").Cells(myrow2, 33)
                  myrow3 = myrow3 + 1

         End If

      Next
End If Next

方法2
Dim myrow, myrow2, myrow3 As Integer

myrow3 = 4

For myrow = 4 To 50

      For myrow2 = 3 To 5000

         If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then
                  'ITO RECIPE
                  Sheets("TEST").Cells(myrow3, 41) = Sheets("資料檔(材料)").Cells(myrow2, 32)
                  'ITES 可RUN 01 OR 02
                  Sheets("TEST").Cells(myrow3, 64) = Sheets("資料檔(材料)").Cells(myrow2, 33)
                  myrow3 = myrow3 + 1
         If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For
         End If

      Next

Next
目前測試兩種方法多可以抓到資料
請問方法2的結構是不是錯的
GBKEE版主辛苦您了!小弟由衷感謝

作者: GBKEE 時間: 2011-3-23 07:13

回復 9# plyl
方法2
If Sheets("TEST").Cells(myrow, 1) = Sheets("資料檔(材料)").Cells(myrow2, 1) Then
            'ITO RECIPE
            If Sheets("TEST").Cells(myrow, 1) = "" Then Exit For  '放這裡才正確             Sheets("TEST").Cells(myrow3, 41) = Sheets("資料檔(材料)").Cells(myrow2, 32)
            'ITES 可RUN 01 OR 02
            Sheets("TEST").Cells(myrow3, 64) = Sheets("資料檔(材料)").Cells(myrow2, 33)
            myrow3 = myrow3 + 1
End If

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)