peter95

回復 5# lpk187


    Sub 擷取()
    Dim Rng As Range, aRng As Range, bRng As Range
    With Sheets("統計")
        .Range("B2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        For Each Rng In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
            Set aRng = Sheets("甲").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
            Set bRng = Sheets("乙").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
            If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
            If Not aRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)
        Next
    End With
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

請問lpk187 大大 這兩個取出的值 (相加) 然後在加上一個負值  並把它固定放在 ...固定G欄的儲存格(從G3開始將值放入..)

說明一下  "這兩個取出的值 (相加) 然後在加上一個負值 "
比如 取出 192  336,,,-(192+336)=-528
再次感謝你的幫忙  謝謝

lpk187

回復 11# peter95

下圖是讓你瞭解Range物件的Offset屬性：運算式.Offset(列的偏移量, 欄的偏移量)

會讓你更容易瞭解程式怎麼運作的

下載 (49.06 KB)

2016-3-16 08:47

1. Option Explicit
   
2. 
   
3. Sub 擷取()
   
4.     Dim Rng As Range, aRng As Range, bRng As Range
   
5.     With Sheets("統計")
   
6.         .Range("B2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
   
7.         For Each Rng In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
   
8.             Set aRng = Sheets("甲").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
9.             Set bRng = Sheets("乙").Columns(1).Find(Rng.Value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=2)
   
10.             If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
    
11.             If Not bRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)
    
12.             Rng.Offset(, 6) = -(Rng.Offset(, 1) + Rng.Offset(, 1))
    
13.         Next
    
14.     End With
    
15. End Sub

複製代碼

lpk187

回復 11# peter95


    還有我第一次PO次的代碼中
If Not aRng Is Nothing Then Rng.Offset(, 1) = aRng.Offset(, 11)
If Not aRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)<<=這句是錯的
應該修正為
If Not bRng Is Nothing Then Rng.Offset(, 2) = bRng.Offset(, 11)