iT邦幫忙

0

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

本人是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

https://ithelp.ithome.com.tw/upload/images/20200628/20128265jUOAxP5Xv0.png

1 個回答

0
海綿寶寶
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

我要發表回答

立即登入回答