Loop Do Until question

[Barry McConnell]

Hello

I have created a macro below, that does what I need (copies down from above
when it finds text "#N/A N/A" in a cell) and I want it to Loop until it has
removed all the "#N/A N/A" values in the worksheet. So any help with the Do
Until section would be very helpful.

Sub CopyDown()

Do Until
Sheets("BLGDataFeedCopy").Select
Columns("D:GQ").Select
Selection.Find(What:="#N/A N/A", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells(ActiveCell.Row - 1, ActiveCell.Column).Select
Range(Selection, Selection.Offset(1, 0)).Select
Selection.FillDown


Loop

End Sub

 

[Joel]

Sub CopyDown()

For Each cell In Sheets("BLGDataFeedCopy").Columns("D:GQ")
if cell.row <> 1 then
If cell.Value = "#N/A N/A" Then
cell.Offset(-1, 0).FillDown _
Destination:=Range(cell.Offset(-1, 0), cell)
End If
End if
Next cell
End Sub

 

[Barry McConnell]

Thanks Joel, I copied your version in but it doesn't get rid of the "#N/A
N/A" values.

 

[Tom Ogilvy]

both of these worked for me:

Sub CopyDown()
With Worksheets("BLGDataFeedCopy")
Set r = Intersect(Columns("D:GQ"), .UsedRange)
End With
For Each cell In r
If cell.Row <> 1 Then
If cell.Value = "#N/A N/A" Then
cell.FillDown
End If
End If
Next cell
End Sub

Sub Copydown1()
With Worksheets("BLGDataFeedCopy")
Set r = Intersect(Columns("D:GQ"), .UsedRange)
End With
Do
Set r1 = r.Find(what:="#N/A N/A", _
After:=r(r.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r1 Is Nothing Then
r1.FillDown
Else
Exit Do
End If
Loop
End Sub

 

[Mike H]

Hi,

probably not the most effecient method but try this. Alter GQ1000 to the
actual range

Sub CopyDown()
For Each c In Sheets("BLGDataFeedCopy").Range("D1:GQ1000")
If c.Value = "#N/A N/A" Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub

Mike

 

[Barry McConnell]

Second one works perfectly. Don't really understand why, but that's fine.
Thanks for your help Tom.

 

[Joel]

I think tyhe problem with my code was I forgot a period

from:
cell.Offset(-1, 0).FillDown _
Destination:=Range(cell.Offset(-1, 0), cell)

to:
cell.Offset(-1, 0).FillDown _
Destination:=.Range(cell.Offset(-1, 0), cell)