Post row from one sheet to another as result of cell value

[hillarymiller]

So I did write a formula on Sheet1 that told me if a number I scanned was on Page 1 list or not. Now I'm trying to figure out how to post the row of that found number on Sheet1. I figured out how to open Visual Basic. Now I need code.

 

[Ezguy4u]

hill Let's get the ball rolling. Here is my solution to your problem. Now if I have missed something or this does not work, let us know.

Sub FindRow()
Dim CellCnt As Integer

CellCnt = Sheets("Page 1").Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To CellCnt

If Sheets("Sheet1").Range("A1") = Sheets("Page 1").Cells(i, 1) Then
    Sheets("Sheet1").Range("B2") = "In Row " & i
    Exit For
End If

Next i

End Sub

Book1
	A	B
1	142-06303
2		In Row 5
Sheet1

Book1
	A
1	Asset
2	069-59957
3	069-59951
4	069-60254
5	142-06303
6	069-59953
Page 1

 

[jolivanes]

Sub Without_Looping()
    With Sheets("Sheet1").Cells(1, 1)
        .Offset(, 1).Value = "Row " & Sheets("Page 1").Columns(1).Find(.Value, , , 1).Row
    End With
End Sub

 

[Peter_SSs]

I did write a formula on Sheet1 that told me if a number I scanned was on Page 1 list or not.

So why not just use another formula for the row?

=IFNA(MATCH(A1,'Page 1'!A:A,0),"n/a")

If you did really want vba then you may need something that avoids erroring if the value is not found on the list. Perhaps this?

Sub Without_Looping_v2()
    With Sheets("Sheet1").Cells(1, 1)
        .Offset(2).Value = Evaluate("ifna(match(""" & .Value & """,'Page 1'!A:A,0),""n/a"")")
    End With
End Sub

 

[jolivanes]

Or maybe easier to understand and change, if required, for a newby.
The WorksheetFunction line checks if the value exists in Column A of Sheet "Page 1"

Sub Without_Looping()
With Sheets("Sheet1").Cells(1, 1)
    If WorksheetFunction.CountIf(Sheets("Page 1").Columns(1), .Value) <> 0 Then
        .Offset(, 1).Value = "Row " & Sheets("Page 1").Columns(1).Find(.Value, , , 1).Row
            Else
        MsgBox "That value does not exist in Page 1 Column A. Sorry."
    End If
End With
End Sub

It looks like a formula would be the way to go for you though.

 

[hillarymiller]

hill Let's get the ball rolling. Here is my solution to your problem. Now if I have missed something or this does not work, let us know.

Sub FindRow()
Dim CellCnt As Integer

CellCnt = Sheets("Page 1").Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To CellCnt

If Sheets("Sheet1").Range("A1") = Sheets("Page 1").Cells(i, 1) Then
    Sheets("Sheet1").Range("B2") = "In Row " & i
    Exit For
End If

Next i

End Sub

Book1
	A	B
1	142-06303
2		In Row 5
Sheet1

Book1
	A
1	Asset
2	069-59957
3	069-59951
4	069-60254
5	142-06303
6	069-59953
Page 1

This one works! But, can I have all of the information from the row i pasted instead of the In Row i???

 

[hillarymiller]

So why not just use another formula for the row?

=IFNA(MATCH(A1,'Page 1'!A:A,0),"n/a")

If you did really want vba then you may need something that avoids erroring if the value is not found on the list. Perhaps this?

Sub Without_Looping_v2()
    With Sheets("Sheet1").Cells(1, 1)
        .Offset(2).Value = Evaluate("ifna(match(""" & .Value & """,'Page 1'!A:A,0),""n/a"")")
    End With
End Sub

This isn't exactly what I'm asking to do. I want to find value A1 in Page 1 and paste the entire row's contents on Sheet1.

 

[jolivanes]

Re: This isn't exactly what I'm asking to do. I want to find value A1 in Page 1 and paste the entire row's contents on Sheet1.

If you don't formulate your questions properly you'll never get what you want.
I assume you want the whole row pasted on sheet 1 in row 1,000,000. Right.

 

[hillarymiller]

Re: This isn't exactly what I'm asking to do. I want to find value A1 in Page 1 and paste the entire row's contents on Sheet1.

If you don't formulate your questions properly you'll never get what you want.
I assume you want the whole row pasted on sheet 1 in row 1,000,000. Right.

I tried to be clear on my request.

I want the whole row from Page 1 pasted on Sheet 1 in row 5.

 

[Peter_SSs]

This one works!

This isn't exactly what I'm asking to do.

I'm confused since both posts referred to do basically the same thing.

I tried to be clear on my request.

On re-reading post 1 I can see that your request could be interpreted differently - we all just chose the wrong interpretation.

I think you could still do it with another formula in A5 of 'Sheet1'

=IFNA(INDEX('Page 1'!A:C,MATCH(A1,'Page 1'!A:A,0),0),"")

However, if you want vba, try this

Sub Get_Row()
  With Sheets("Sheet1")
    If .Range("A2").Value = "On list" Then
      Sheets("Page 1").Columns("A").Find(What:=.Range("A1").Value, LookAt:=xlWhole).EntireRow.Copy Destination:=.Range("A5")
    End If
  End With
End Sub