Do while loop issue

**Licquor** · 04-29-2008, 09:57 AM

Hi, I am trying to pull out various subtotals from a list terminating with the word "End". My code finds the first instance only although both the counter and the rowpos increment correctly. I am beginning to lose the will to live resolving this

_________________________________________________________________

Sub Activities()
Sheets("Report Paste").Select
Counter = 1
RowPos = 10
Do While Cells(Counter, 1) <> "End"
    If Cells(Counter, 2) = "Total" Then
        Range(Cells(Counter, 1), Cells(Counter, 3)).Select
        Selection.Copy
        Sheets("Weekly Calculator").Select
        Range(Cells(RowPos, 10), Cells(RowPos, 12)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        RowPos = RowPos + 1
    End If
    
    Counter = Counter + 1
Loop

End Sub

_________________________________________________________________

Any assistance with this would be appreciated.

**Leith Ross** · 04-29-2008, 11:19 AM

Hello Licquor,

This is happening because you don't re-select "Report-Paste" after you have pasted the data. Here is a better method of coding that doesn't involve selecting the ranges or sheets.

Sub Activities()
 
  Dim Counter As Long
  Dim DstWks As Worksheet
  Dim RowPos As Long
  Dim SrcWks As Worksheet

    Set SrcWks = Sheets("Report Paste")
    Set DstWks = Sheets("Weekly Calculator")

    Counter = 1
    RowPos = 10

      Do While SrcWks.Cells(Counter, 1) <> "End"
        If SrcWks.Cells(Counter, 2) = "Total" Then
           SrcWks.Cells(Counter, 1).Resize(1, 3).Copy Destination:=DstWks.Cells(RowPos, 10)
           RowPos = RowPos + 1
        End If  
        Counter = Counter + 1
      Loop

End Sub

Sincerely,
Leith Ross

Do while loop issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Do while loop issue

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions