excel and vba

[Stephen Warker]

hey everyone,

I have a macro that runs looks like this:

-**********************************-
Sub B()
Dim rng As Range, rng2 As Range, cell As Range

Set rng = Range("F4.F66")
'rng contains the values of the amount column
Status = 0
For Each cell In rng
If cell >= 1 Then
Range("b" & cell.Row, "g" & cell.Row).Select
'selectst he columns B,C,D,E,F,G columns of the row with an amount greater than $1
ActiveSheet.Copy
'put in to scroll to b101
Range("B10" & cell.Row).Select
ActiveSheet.Paste

End If
Next cell

Set rng = Nothing
Set cell = Nothing
End Sub
-****************************************-

where i say i want to paste the copied data into b101 is not correct. I need to be able to paste the records it finds starting at b101, but depending on the number of records, i must be able to paste the second data in b102, the third in b103 and so on. And help would be greatly appriciated.

thanks,
Steve

[(B2F)Tom]

When you copy the fields founded at b101, doesn't the records already put in b102, b103, ...

What's happened when you execute your macro?

Maybe you can count the number of records* in your result and affect each record like this:

For Index=1 to RecordsCount*
     Range("B10" & Index).Select  ' Select each records !
     ActiveSheet.Paste 
next

[Mongo]

Sub CopyIt()
Dim hits As Integer
Dim rng As Range, cell As Range
  Set rng = ActiveSheet.Range("F4:F66")
  hits = 0
  For Each cell In rng
    '  more efficient way of saying >= 1...
    If cell > 0 Then
      Range("B" & cell.Row & ":G" & cell.Row).Copy Range("B" & (101 + hits))
      hits = hits + 1
    End If
  Next cell
  Set rng = Nothing
  Set cell = Nothing
End Sub