jeudi 22 décembre 2016

VBA - Create loop for set of active cells with If command

I'm having some difficulty applying the If command I want to execute for all rows of a selected range. So far I've got this:

  Dim x As Integer

  NumRows = Range("J2", Range("J2").End(xlDown)).Rows.Count

  Range("J2").Select

  For x = 1 To NumRows
    If ActiveCell.Value = "9995" Then
    ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(0, 3).Value
    Else: ActiveCell.EntireRow.Delete
    End If
     ActiveCell.Offset(1, 0).Select
  Next

It seems to have no problem selecting a range of cells from J2 down to the end of the list of data. It also has no problem finding cells in this range which contain "9995" and executing the command to copy the data 3 cells to the right off the "9995" cell and paste it 7 cells to the right of the "9995" cell. It even deletes the entire row where the active cell value is not "9995" (which is exactly what I want), but only in some cases. It leaves some rows for which the active cell value is not "9995" and I can't understand why.

Can anyone help?

Thanks!

Aucun commentaire:

Enregistrer un commentaire