Select certain numbers high and move them to another column (VBA code)

[noodle48]

I need to look through column B at dollar amounts, I need only the ones that are $500 or more and place their values in column E. Also I need to take their name of the user in column A as well but the names will be in column D.

Thanks in advance.

[NBVC]

Add a help column first.

Eg

in E2 enter:

=IF(B2>=500,COUNT(E$1:E1)+1,"")

copied down

Then in a separate column, one cell, say in F2, enter =MAX(E:E)

To extract data now...

=IF(ROWS($A$1:$A1)>$F$2,"",INDEX(A:A,MATCH(ROWS($A$1:$A1),$E:$E))

copied down as far as you want...

Then copied across to get Column B, C, D corresponding values.

[noodle48]

Can we make this into a vba code?

[NBVC]

Perhaps someone else can....

[tigeravatar]

noodle48,

Something like this? (assumes row 1 has headers)

Sub tgr()
    
    [D2].Resize([D2].CurrentRegion.Rows.Count, 2).ClearContents
    Dim RowIndex As Long, Over500 As Range
    For RowIndex = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        If IsNumeric(Cells(RowIndex, "B").Value) And Cells(RowIndex, "B").Value >= 500 Then
            If Over500 Is Nothing Then
                Set Over500 = Cells(RowIndex, "A").Resize(1, 2)
            Else
                Set Over500 = Union(Over500, Cells(RowIndex, "A").Resize(1, 2))
            End If
        End If
    Next RowIndex
    Over500.Copy
    [D2].PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
End Sub

Hope that helps,
~tigeravatar

[noodle48]

Thank you very much, this does exactly what I wanted to do.