Help me find data, copy it and paste from array

[instanceoftime]

I would like to find the first complete "row" in the array that didn't contain blanks (A-H) and paste that info to the end of the spreadsheet.

Private Sub cmdEnter_Click()

    Dim str1 As String, str2 As String, str3 As String, str4 As String, str5 As String, str6 As String, str7 As String, str8 As String
    Dim NotFound As Integer
    Dim I As Long


    NotFound = 0    
    
    ActiveWorkbook.Sheets("Items").Activate


            With ActiveSheet
                arr = .Range("A1:H" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            End With
    
            For I = 1 To UBound(arr)
                If arr(I, 1) = ItemNumber Then  'multiple AND statements here?????
                    str1 = IIf(str1 = "", arr(I, 1), str1 & "|" & arr(I, 1)) 'item number
                    str2 = IIf(str2 = "", arr(I, 2), str2 & "|" & arr(I, 2)) 'description
                    str3 = IIf(str3 = "", arr(I, 3), str3 & "|" & arr(I, 3)) 'id
                    str4 = IIf(str4 = "", arr(I, 4), str4 & "|" & arr(I, 4)) 'date
                    str5 = IIf(str5 = "", arr(I, 5), str5 & "|" & arr(I, 5)) 'dept#
                    str6 = IIf(str6 = "", arr(I, 6), str6 & "|" & arr(I, 6)) 'dept
                    str7 = IIf(str7 = "", arr(I, 7), str7 & "|" & arr(I, 7)) 'upc
                    str8 = IIf(str8 = "", arr(I, 8), str8 & "|" & arr(I, 8)) 'price                


                End If
            Next
            
End Sub

 

[JLGWhiz]

Any reason why this won't work?

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
        If Application.CountA(c.Resize(1, 8)) = 8 Then
            c.Resize(1, 8).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End With
End Sub

 

[instanceoftime]

Oh, Wow ... I left info out. I am searching column A for ItemNumber. when found .. If column G of that row isn't empty I need to copy the row to the end of the spreadsheet (just once and be done)

my appologies

 

[JLGWhiz]

This will error unless you have ItemNumber defined in a Public variable somewhere. I could not find it anywhere in youre posts.

Sub t()
Dim fn As Range
With ActiveSheet
    Set fn = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Find([COLOR=#B22222]ItemNumber[/COLOR], , xlValues, xlWhole) [COLOR=#B22222]'variable not defined[/COLOR]
        If Not fn Is Nothing And fn.Offset(, 6) <> "" Then
            fn.Resize(1, 8).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
        End If            
End With
End Sub

 

[instanceoftime]

Perfect. Works great ... was hoping to infuse it within the array for other aspects of the code but your answer is Perfect! thanks so much. My brain was starting to fizzle

 

[instanceoftime]

in that row just copied I want to change the value in G to "NewUPC" and H to "NewPrice". is there an easy way to reference those cells? (NewPrice and NewUPC) are variables

 

[instanceoftime]

The following code works for what I want it to do. Thanks

Private Sub cmdEnter_Click()


    Dim fn As Range
    Dim LastRow As Long
    Dim Price As Currency
    
    Price = txtbxPrice.Text


    With ActiveSheet
        Set fn = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Find(ItemNumber, , xlValues, xlWhole)
            If Not fn Is Nothing And fn.Offset(, 6) <> "" Then
                fn.Resize(1, 8).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Cells(LastRow, 7).Value = (UPCNumber)
        Cells(LastRow, 8).Value = (Price)
    End With




End Sub

 

[JLGWhiz]

The following code works for what I want it to do. Thanks

Private Sub cmdEnter_Click()


    Dim fn As Range
    Dim LastRow As Long
    Dim Price As Currency
    
    Price = txtbxPrice.Text


    With ActiveSheet
        Set fn = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Find(ItemNumber, , xlValues, xlWhole)
            If Not fn Is Nothing And fn.Offset(, 6) <> "" Then
                fn.Resize(1, 8).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Cells(LastRow, 7).Value = (UPCNumber)
        Cells(LastRow, 8).Value = (Price)
    End With




End Sub

Glad you worked it out.
Regards, JLG