if and vb code

[jaypatel]

Hi all,

What would the vb code be for the following:
Where in column A, cell = “caseid” then delete the row above. Ie if cell a20=”caseid”, delete row 19

and

Where in column F, cell = “Commission due” drop that particular cell down 1 cell. Ie if commission due in cell F16, move cell F16 to F17;

thanks

Jay

 

[Andrew Poulsom]

What have you tried? Do you want to test column F before column A? If A17 contains "caseid" and F16 contains "Commision due", deleting row 16 would lose what's in F16.

 

[jaypatel]

oh, sorry,

these are mutually exclusive, ie they would be worked on different sheets, but need the code to help with the analysis

Jay

 

[Andrew Poulsom]

Try:

Sub TestA()
    Dim r As Long
    With ActiveSheet.Range("A1").CurrentRegion
        r = .Rows.Count
        Do While r >= 2
            With .Cells(r, 1)
                If .Value = "caseid" Then
                    .Offset(-1).EntireRow.Delete
                    r = r - 1
                End If
                r = r - 1
            End With
        Loop
    End With
End Sub

Sub TestF()
    Dim r As Long
    With ActiveSheet.Range("A1").CurrentRegion
        r = .Rows.Count
        Do While r >= 2
            With .Cells(r, 6)
                If .Value = "Commission due" Then
                    .Offset(-1).Cut .Offset(0)
                    r = r - 1
                End If
                r = r - 1
            End With
        Loop
    End With
End Sub

 

[jaypatel]

Hi Andrew

I have tried the code, but the expected result has not happened.... actually nothing has happened when i run either of them.

I have checked whether it is case sensitive as well.

Sorry

Jay

 

[Andrew Poulsom]

Both procedures worked when I test them. The code does assume that your data starts in A1 and is contiguous. Maybe it will help if you post a sample of your data.