CODEWITHSUNDEEP
vbaexcel
user1204868
user1204868

Reputation: 606

Copy a range of cells after meeting criteria

I need to do a copy of a range of cell after meeting the conditions as follows:

whenever Cells(15,15) of sheet1 is equals to cells(14,j). Firstcell will be equals to cells(14,j). Same concepts for secondcell.

After that I will need to do a copy of the cells between Firstcell and Secondcell. This is when I start to see the runtime error 91.

Assume that I have defined lastCol. My code:

Dim firstcell as range, secondcell as range

If Sheets("Sheet1").Cells(15, 10) <> "" And Sheets("Sheet1").Cells(15, 15) <> "" Then

    For j = 10 To lastCol

        If Sheets("Sheet1").Cells(15, 15).Text = Sheets("Sheet1").Cells(14, j).Text Then

            firstcell = Sheets("Sheet1").Cells(15, j)

        End If

        If Sheets("Sheet1").Cells(15, 13).Text = Sheets("Sheet1").Cells(14, j).Text Then

            secondcell = Sheets("Sheet1").Cells(15, j)

        End If

    Range(firstcell, secondcell ).copy

    Next j

End If

Upvotes: 1

Views: 75

Answers (1)

Sobigen
Sobigen

Reputation: 2169

Answer determined from comments:

Dim firstcell as range, secondcell as range

If Sheets("Sheet1").Cells(15, 10) <> "" And Sheets("Sheet1").Cells(15, 15) <> "" Then
    For j = 10 To lastCol
        If Sheets("Sheet1").Cells(15, 15).Text = Sheets("Sheet1").Cells(14, j).Text Then
            set firstcell = Sheets("Sheet1").Cells(15, j)
        End If

        If Sheets("Sheet1").Cells(15, 13).Text = Sheets("Sheet1").Cells(14, j).Text Then
            set secondcell = Sheets("Sheet1").Cells(15, j)
        End If
    Next j
    Range(firstcell, secondcell ).copy
End If

Upvotes: 2

Related Questions