Loop Through Columns: Search for Value Greater Than 0: Write to Another Worksheet When Value Found and Continue Loop Until Last Row RRS feed

  • Question

  • Hey Guys,

    This is my third day with VBA, so i am a complete Newbie and not surprisingly am having some trouble. I am trying to write a section of code that:

    1. Loops through cells of a given range and searches for a value greater than 0. 

    2. If Value meets above condition then copy that value into another cell of a different sheet

    3. Continue searching and copying cells until all values from that range have been found and copied

    This Loop is also within a For Next Loop that goes row by row and copies other values into another sheet until it finds the last row where it will end

    I have set up a MsgBox test to see what values the  initial Loop (one  searching for values)is finding and for the most part it seems to be working correctly and returning the right values but after some values it returns a blank box when it should be returning the next found value

    I know it is not pretty , but hey third day. Any pointers, tips suggestions and criticisms are welcomed and appreciated. Do not need the answer just direction and some hints

    Here is the Code:

    Dim RangeR As Range
    Dim Start As Integer
    Dim FirstLine As Integer
    Dim StartRow As String
    Dim NextRow As Integer
    Dim LastLine As Integer
    Dim NewFirstLine As Integer
    Dim DFirst As Range
    Dim WLast As Range
    StartRow = InputBox("What row contains the first model?")
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    NewFirstLine = 2

    For StartRowNumber = Val(StartRow) To LastRow Step 1

    Set DFirst = Range("D" & StartRowNumber)
    Set WLast = Range("W" & StartRowNumber)

        NumberofSizes = WorksheetFunction.CountA(Range("D" & StartRowNumber, "W" & StartRowNumber))
        LastLine = (NewFirstLine + NumberofSizes) + 1
                    For Each RangeR In Range(DFirst, WLast)
                        If RangeR.Value > 0 Then
                        MsgBox RangeR
                            Worksheets("sheet1").Range("R" & NewFirstLine + 1).Value = RangeR.Value
                        End If
                    Next RangeR

                    For FirstLine = NewFirstLine To LastLine Step 1
                         Worksheets("sheet1").Range("Q" & FirstLine).Value = Range("A" & StartRowNumber)
                         Worksheets("sheet1").Range("P" & FirstLine).Value = Range("B" & StartRowNumber)
                         Worksheets("sheet1").Range("N" & FirstLine).Value = Range("C" & StartRowNumber)
                         Worksheets("sheet1").Range("F" & FirstLine).Value = Range("AE" & StartRowNumber)
                    Next FirstLine
        Application.EnableCancelKey = xlInterrupt
        NewFirstLine = LastLine

    Next StartRowNumber

    Wednesday, August 8, 2012 8:48 PM

All replies

  • I can't work out what you are trying to achieve.

    The code in the loop "For Each RangeR In Range(DFirst, WLast)" only processes if "RangeR.Value > 0". (Controlloed by If function)

    The code in the loop " For FirstLine = NewFirstLine To LastLine Step 1" runs irrespective of the value of RangeR. Is this what it is supposed to do?

    Regards, OssieMac

    Thursday, August 9, 2012 4:21 AM