lundi 10 septembre 2018

If Else with 2 conditions and using result in the if and else part

I have this code it's supposed to check a certain range in the third column for the value 4 and if a cell has the value 4 it's supposed to check the value of the adjacent cell in the fourth column. If this value is smaller than y it's supposed to assign the value 4 to the 5 cells in the 15th column starting in the row in which the 4 was initially found. If it's higher than it should assign it to 8 cells in column 15 starting in the row in which the 4 was initially found.

The problem that keeps occuring is that the cell specification in the Else part for example .Cells(x - 1, a).Value = 5results in an error. If I only use the If portion it works fine.

Sub einfügen()

Dim a As Integer
Dim x As Integer
Dim y As LongLong

a = 15
y = 100000


With ActiveWorkbook.ActiveSheet

For x = 1 To 112
    If .Cells(x, 3).Value = 4 And ActiveWorkbook.ActiveSheet.Cells(x, 3).Offset(0, 1) <= y Then

            .Cells(x - 1, a).Value = 4
            .Cells(x - 2, a).Value = 4
            .Cells(x - 3, a).Value = 4
            .Cells(x - 4, a).Value = 4
            .Cells(x - 5, a).Value = 4
    Else

            .Cells(x - 1, a).Value = 4
            .Cells(x - 2, a).Value = 4
            .Cells(x - 3, a).Value = 4
            .Cells(x - 4, a).Value = 4
            .Cells(x - 5, a).Value = 4
            .Cells(x - 6, a).Value = 4
            .Cells(x - 7, a).Value = 4
            .Cells(x - 8, a).Value = 4


   End If


   Next

End With
End Sub

Aucun commentaire:

Enregistrer un commentaire