Excel VBA參照上方列最近的數字，決定是否要執行

excel vba #excel #vba

tina76 2020-06-28 22:36:23 ‧ 1284 瀏覽

分享至

本人是Excel VBA小菜菜。Google好久可能的方法，但功力有限。
希望高手能幫忙解答。謝謝。

現在卡關在B欄LV是3或A的，則複製對應欄到另一分頁欄位。
但A的部分，我只想要3下方出現的A，4、5、6(非3的)下方出現的A就不要抓，已經想破頭不知道還可以用什麼語法了。
Sub CopyRows()
Dim X As Integer
Dim Cnt As Integer
Cnt = 2

For X = DCLocRow + 1 To TotalRow  'Loop through each row
    LVValue = Cells(X, 2).Value 'Decide if to copy based on column B
    If LVValue = "3" Then
    Sheets("Result").Cells(Cnt, 2).Value = Sheets("Sheet1").Cells(X, 3).Value
    Sheets("Result").Cells(Cnt, 4).Value = Sheets("Sheet1").Cells(X, 2).Value
    Sheets("Result").Cells(Cnt, 3).Value = Sheets("Sheet1").Cells(X, 7).Value
    Sheets("Result").Cells(Cnt, 1).Value = Sheets("Sheet1").Cells(X, 1).Value
    Cnt = Cnt + 1
    
    ElseIf LVValue = "A" Then
    Sheets("Result").Cells(Cnt, 2).Value = Sheets("Sheet1").Cells(X, 3).Value
    Sheets("Result").Cells(Cnt, 4).Value = Sheets("Sheet1").Cells(X, 2).Value
    Sheets("Result").Cells(Cnt, 3).Value = Sheets("Sheet1").Cells(X, 7).Value
    Sheets("Result").Cells(Cnt, 1).Value = Sheets("Sheet1").Cells(X, 1).Value
    Cnt = Cnt + 1

    ElseIf LVValue = "2" Then
        Exit For
    End If
Next X

End Sub

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2020-06-29 09:01:42

最佳解答

加一個 CopyValue 來判斷
修改如下

Sub CopyRows()
Dim X As Integer
Dim Cnt As Integer
Dim CopyValue As Boolean
Cnt = 2

For X = DCLocRow + 1 To TotalRow  'Loop through each row
    LVValue = Cells(X, 2).Value 'Decide if to copy based on column B
    If LVValue = "3" Then
	    Sheets("Result").Cells(Cnt, 2).Value = Sheets("Sheet1").Cells(X, 3).Value
		Sheets("Result").Cells(Cnt, 4).Value = Sheets("Sheet1").Cells(X, 2).Value
		Sheets("Result").Cells(Cnt, 3).Value = Sheets("Sheet1").Cells(X, 7).Value
		Sheets("Result").Cells(Cnt, 1).Value = Sheets("Sheet1").Cells(X, 1).Value
		Cnt = Cnt + 1
    
    ElseIf LVValue = "A" Then
    	If CopyValue = True Then
			Sheets("Result").Cells(Cnt, 2).Value = Sheets("Sheet1").Cells(X, 3).Value
			Sheets("Result").Cells(Cnt, 4).Value = Sheets("Sheet1").Cells(X, 2).Value
			Sheets("Result").Cells(Cnt, 3).Value = Sheets("Sheet1").Cells(X, 7).Value
			Sheets("Result").Cells(Cnt, 1).Value = Sheets("Sheet1").Cells(X, 1).Value
			Cnt = Cnt + 1
		End If
		
    ElseIf LVValue = "2" Then
        Exit For

    End If
    
    'Save current status - 3:On A:Nop Others:Off
    If LVValue = "3" Then
    	CopyValue = True
    ElseIf LVValue = "A" Then
    Else
    	CopyValue = False
    End If
Next X

End Sub