VB returning cells of fount results

Closed
wil - Sep 14, 2009 at 10:08 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 16, 2009 at 11:14 PM
Hello,
Fairly new to VB so could do with alittle help, I am trying to build a data base
What I have is a few rows of data, I want to search one column for data between a criteria ( 70 -71), find this data and then return the whole row back to a different range. I am having real trouble with the last bit, I can get it to return the data but in the form of first result, second result etc… can anyone suggest a correction for this?

Private Sub CommandButton1_Click()
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Dim Si As Variant
Dim i As Variant

For Si = 70 To 71
With Range("F1:F31")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Range("F1:F31").find(Si, after:=LastCell)

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If

Do Until FoundCell Is Nothing
For i = 40 To 50
Range("a40:AD50", Cells(i, 1)) = FoundCell.EntireRow.Value
Next i

Set FoundCell = Range("F1:F31").FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
Next


End Sub

Configuration: Windows XP Internet Explorer 6.0
Read moreBest

1 response

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 16, 2009 at 11:14 PM
Hello

I think you need to change this code
Range("a40:AD50", Cells(i, 1)) = FoundCell.EntireRow.Value

From your query" return the whole row back to a different range" you want to copy an entire row
for this rows(number1).copy
rows(number2).select
activesheet.paste
will copy the entire row
0