VBA If with two criteria in two columns

[JZentner]

I need to look at two cells (C and F) on a each row, and if the value for C to ends with 30 and the value for F is greater than Zero, copy and paste the row to another sheet. I've managed to get the copy and paste to work using 1 criteria, but I cannot figure out how to get both criteria to work together.

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] compile1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] x [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] y [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] rSearch [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Application"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"C:c"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] rSearch1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Application"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"F:F"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#858C93]'x = rSearch.Cell.Value[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'y = rSearch1.Value[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] cell [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] rSearch
x [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] cell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Right[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]x[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"30"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] cell1 [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] rSearch1
y [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] cell1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] y [/COLOR][COLOR=#303336]>[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]

 [/COLOR][COLOR=#858C93]' If Right(x, 2) = "30" And y > 0 Then[/COLOR][COLOR=#303336]

        matchRow [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] cell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Row
        Rows[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]matchRow [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]":"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] matchRow[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
        Selection[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Copy

        Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"sheet2"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
        ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]matchRow[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
        ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Paste
        Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Application"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
 [/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#242729][FONT=Arial]

 

[abhi1687]

replace:

[FONT=Consolas][COLOR=#303336]x [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] cell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Right[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]x[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7d2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7d2727]"30"[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] cell1 [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] rSearch1y [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] cell1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] y [/COLOR][COLOR=#303336]>[/COLOR][COLOR=#7d2727]0[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
[/COLOR][/FONT]

with:

If Right(cell, 2) = "30" And cell.Offset(, 3) > 0 Then

 

[JZentner]

Hi, with this amendment it now correctly omits rows where the value in col F is negative, however it is pulling through values where the last two digits in Col F happen to be 30. What I need is for only Col C's last two digits to be 30, and Col to be greater than 0.

 

[abhi1687]

I understand that you want to look for last 2 characters in column C to be 30 and column F should be greater than 0 then only copy that row. if that is correct, then it should work but make sure the For each line is referring to column C. if still not working post your amended code will check it

 

[JZentner]

Works!!

<code>Sub compile1()
Set rSearch = Sheets("Application").Range("C:C")


For Each cell In rSearch


If Right(cell, 2) = "30" And cell.Offset(, 3) > 0 Then


matchRow = cell.Row
Rows(matchRow & ":" & matchRow).Select
Selection.Copy


Sheets("sheet2").Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets("Application").Select
End If


Next


End Sub


</code>