Loop Do Until question
 

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

Loop Do Until question
 

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" wrote:

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

Loop Do Until question
 

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

"Joel" wrote:



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" wrote:

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

Loop Do Until question
 

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

--
Regards,
Tom Ogilvy


"Barry McConnell" wrote:

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

"Joel" wrote:



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" wrote:

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

Loop Do Until question
 

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" wrote:

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

"Joel" wrote:



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" wrote:

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

Loop Do Until question
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Barry McConnell" wrote:

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

"Joel" wrote:



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" wrote:

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

Loop Do Until question
 

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)


"Barry McConnell" wrote:

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

"Joel" wrote:



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" wrote:

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