Please help

[sympatico]

Hey everyone,

Im trying to loop through a column of values and if they equal to a certain
value, print the list of relevant values on a different sheet.

e.g. somthing along these lines

Dim number

Sheets("Info").Select
number = Range("B2").Select

Range("A2").Select

Do Until IsEmpty(ActiveCell)

If (ActiveCell = number) Then

Selection.Offset(0, 2).Copy

!-- then i want to copy in a different sheet this value and move to the next
value, then copy the next value into the other sheet and so on --!

End If

Loop

 

[Jack Sheet]

Not the most elegant solution I expect, but:

Sub CopyMatches()
Dim lRowSource As Long
Dim lRowDestination As Long
lRowSource = 2
lRowDestination = 2
While Not IsEmpty(Worksheets("Info").Cells(lRowSource, 1))
If Worksheets("Info").Cells(lRowSource, 1).Value = _
Worksheets("Info").Cells(2, 2).Value Then
Worksheets("Report").Cells(lRowDestination, 1).Value = _
Worksheets("info").Cells(lRowSource, 3).Value
lRowDestination = lRowDestination + 1
End If
lRowSource = lRowSource + 1
Wend
End Sub

 

[JE McGimpsey]

I'm not sure I understand you correctly, but this may help:

Dim rCell As Range
Dim rDest As Range
Dim rSource As Range
Dim dNumber As Double

Set rDest = Sheets("Sheet2").Range("A1")
With Sheets("Info")
Set rSource = .Range("A2:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
dNumber = .Range("B2").Value
End With
For Each rCell In rSource
With rCell
If .Value = dNumber Then
.Offset(0, 2).Copy Destination:=rDest
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell