2018-06-25

網路有人提的問題 , 來解解看

判斷式只要碰到”合併儲存格”就會只判斷第一列，請問要怎樣調整公式?謝謝。

需求:如果G行有字串”DOG”，則在M行打V，否則打X

M行判斷式:=IFERROR(IF(SEARCH("DOG",G2),"V",),"X")

我的只有 VBA 這辦法

Sub test()
lr = Cells(1, 6).End(xlDown).Row
B = 0
For xl = 2 To lr
        If Cells(xl, 7).MergeCells = True Then
            If Not (Cells(xl, 7).Value = "") Then
                B = 0
            End If
            If Cells(xl - B, 7).Value = "DOG" Then
                Cells(xl, 13).Value = "V"
                B = B + 1
            Else
                Cells(xl, 13).Value = "X"
                B = B + 1
            End If
        ElseIf Cells(xl, 7).Value = "DOG" Then
            Cells(xl, 13).Value = "V"
            B = 0
        ElseIf Not (Cells(xl, 7).Value = "DOG") Then
            Cells(xl, 13).Value = "X"
            B = 0
        End If
 Next xl
End Sub