Simple loop problem

[Christy]

I have a simple loop that is not working and I just see why. I am trying to
loop through column F and if there is an X in the cell, copy the value from
column A in the same row to another worksheet. I loop is only picking up the
last x in column F?
Any help would be greatly appreciated.

Sub DailyList()
Dim WorkRange As Range
Dim i As Integer
Set WorkRange = Sheet3.Range("a300").End(xlUp).Offset(1, 0)
i = 2
For i = 2 To 300 Step 1
If Sheet1.Cells(i, 6) = "x" Then
WorkRange.Value = Sheet1.Cells(i, 1).Value
End If
Next
End Sub

 

[Christy]

Thank goodness it's Friday! I see I forgot to put a .value in my code

 

[Don Guillett]

what you are doing here is copying ALL to the same cell instead of the next
available cell.

WorkRange.Value = Sheet1.Cells(i, 1).Value

 

[Toppers]

Hi,
You need to increment WorkRange to point to next row

Sub DailyList()
Dim WorkRange As Range
Dim i As Integer
Set WorkRange = Sheet3.Range("a300").End(xlUp).Offset(1, 0)
i = 2
For i = 2 To 300 Step 1
If Sheet1.Cells(i, 6) = "x" Then
WorkRange.Value = Sheet1.Cells(i, 1).Value
Set WorkRange = WorkRange.Offset(1, 0)
End If
Next
End Sub

 

[galimi]

Christy,

Try the following

Sub Daily()

Set oFind = shtFind.Range("f1")
Set oCopy = shtCopy.Range("a1")

Do Until oFind.Offset(i).Row > shtFind.UsedRange.Rows.Count
If oFind.Offset(i) = "x" Then
oCopy.Offset(i) = oFind.Offset(i, -5)
End If
i = i + 1
Loop

End Sub

You did not set an adjustable offset for your workrange object. My code
will also take into account any sized spreadsheet.

 

[Bob Phillips]

That shouldn't be the reason, Value is the default property.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Christy]

Thank you all very much!

what you are doing here is copying ALL to the same cell instead of the next
available cell.

WorkRange.Value = Sheet1.Cells(i, 1).Value