Simple loop problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
 
That shouldn't be the reason, Value is the default property.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thank you all very much!

Don Guillett said:
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
Click to expand...
 
Back
Top