Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Loop question | Excel Programming | |||
Loop question | Excel Discussion (Misc queries) | |||
Next and Loop Question | Excel Programming | |||
Loop question | Excel Programming | |||
loop question | Excel Programming |