Hello,
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
I wonder whether you can find values between two numbers by using "find" function.
Sub test()
Dim rng As Range, c As Range, dest As Range
With Worksheets("sheet1")
Set rng = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In rng
If c >= 70 And c <= 71 Then
c.EntireRow.Copy
Else
GoTo line1
End If
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
line1:
Next c
End With
End Sub
|
Check cell value excel vba Code to run a macro when a cell value is updated Delete rows that don't contain cell value Excel vba returning the last cell address in a range Excel vba returning the same cell color value Find last cell copy past How to set and retrieve the particular cell value [solved Lookup a number then return another cell value if true Next cell.value find next empty cell Pivot table original cell value Using vba to find cell value Vba code to get cell values of sheet1 in sheet 2 Vba copy and paste cell into other workbook Vba excel find cell reference contain certian value Vba how to read cell date and if then statement Vba return string exec function Vba run code when a cell value in a workbook changes Visual basic excel macro get cell value