VB returning cells of fount results [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
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 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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