Help needed - lookup two conditions within array, return results and add row

[bovril33]

Hi,

I am fairly new to VBA and am looking for some advice. I am trying to write code that goes through two ranges in two separate columns, checks against a condition and returns a value if true. Crucially, for each value I'd like it to add a row below and return the next value.

For the first part I had the following code:

Sub fillOB1()

nextRow = 5

For x = 6 To 69
If Sheets(1).Cells(x, 5) > "1" And Sheets(1).Cells(x, 15) > "1" Then

Sheets(2).Cells(nextRow, 4).Value = Sheets(1).Cells(x, 2).Value
Sheets(2).Cells(nextRow, 5).Value = Sheets(1).Cells(x, 3).Value

nextRow = nextRow + 1
End If
Next x

End Sub

But this relies on the output table having enough rows to contain all true values. However as there is another table below the output table and I don't want it to spill over, I thought about adding new rows underneath each value. I tried to do this using the ActiveCell function, but ran into compiling issues:

Sub fillOB1()

Sheets(2).Select
Range(D5).Select

For x = 6 To 69
If Sheets(1).Cells(x, 5) > "1" And Sheets(1).Cells(x, 15) > "1" Then
Sheets(2).Cells(ActiveCell, 4).Value = Sheets(1).Cells(x, 2).Value
Sheets(2).Cells(ActiveCell.Offset(0, 1), 5).Value = Sheets(1).Cells(x, 3).Value

ActiveCell.Offset(1).EntireRow.Insert

Range (ActiveCell.Offset(1, -1))

End If
Next x


End Sub

Can anyone tell me what I am doing wrong, or suggest an alternative method?

Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Sub fillOB1()
    Dim nextrow As Long
    Dim x As Long
    nextrow = 5
    For x = 6 To 69
        If Sheets(1).Cells(x, 5) > "1" And Sheets(1).Cells(x, 15) > "1" Then
            Sheets(2).Cells(nextrow, 4).Value = Sheets(1).Cells(x, 2).Value
            Sheets(2).Cells(nextrow, 5).Value = Sheets(1).Cells(x, 3).Value
            Sheets(2).Cells(nextrow, 1).Offset(1).EntireRow.Insert
            nextrow = nextrow + 1
        End If
    Next x
End Sub

 

[bovril33]

Perfect thanks a lot Andrew!