how to select a section of ranges

问题:

To assign xx in the AP column, i have to check some fields that they must be filled before the xx assignment

i tried to assign some columns to a variable and i did the control to that variable, but it seems like it doesn't work

I got an error on this line

Set MaPlage = Columns("A:R" & "W:E").Rows(i)

this is my code :

Sub Decision()


Dim cell As Range

Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim u As Integer
Dim t As Integer
        Dim l As Integer
        Dim p As Integer

        Set MaPlage = Columns("A:R" & "W:E").Rows(i)

 For i = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    If CStr(ActiveSheet.Cells(i, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" And (ActiveSheet.MaPlage.Value) = "<>" Then

        If CStr(ActiveSheet.Cells(i, 14).Value) = "AEP" _
            Or CStr(ActiveSheet.Cells(i, 14).Value) = "CMC_REV" _
            Or CStr(ActiveSheet.Cells(i, 14).Value) = "CMC_APT" _
            Or CStr(ActiveSheet.Cells(i, 14).Value) = "CS_TPD" _
            Or CStr(ActiveSheet.Cells(i, 14).Value) = "DM_ID" Then

                         ActiveSheet.Cells(i, 42).Value = "XX"
        End If


    End If

Next i

回答1:

Combining all the comments try this:

For i = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    Set maplage = Range("A" & i & ":H" & i & ",J" & i & ":R" & i & ",W" & i & ":Z" & i & ",AA" & i & ":AO" & i)
    If UCase(Left(ActiveSheet.Cells(i, 31).Value), 3) = "COM" _
         And WorksheetFunction.Countif(MaPlage,"") = 0 Then
        Select Case UCase(Left(ActiveSheet.Cells(i, 14).Value), 3)
            Case "AEP", "CMC", "CS_", "DM_"
                ActiveSheet.Cells(i, 42).Value = "XX"
        End Select
    End If
Next i