Looping through a big range, when conditions are met take the value to the last spot in a list.

[walmer26]

Hello Guys,

I hope I can find a light with some loop. I am writing a code for a template and I am having issues to achieve something. I am trying to look at one specific column in one sheet of the template and when the value is > than 0 I need the value to be placed at the end of a list that I have in a different sheet same file.

I am doing the following...

CountResults = Application.WorksheetFunction.CountIf(Sheets("MASTER ROOMS RECONCILE").Range("L:L"), ">" & 0)
LastRow = Sheets("MASTER ROOMS RECONCILE").Cells(Cells.Rows.Count, "C").End(xlUp).Row
LastRow2 = Cells(Cells.Rows.Count, "I").End(xlUp).Row

'In the this 1st loop I put the limits equal to the number of results, as I am trying to hace 1 line per result in other sheet
For n = 1 To CountResults
'In this 2nd loop I say that for each value found (that should be equal to my CountResults) if meet the condition >0 put one line for each found result
'that line found has 1 row with 5 columns data as you can see below
For Each c In Sheets("MASTER ROOMS RECONCILE").Range("L2:L" & LastRow)
If c.Value > 0 Then
Range("I" & (LastRow2 + n)).Value = c.Offset(0, -4).Value
Range("J" & (LastRow2 + n)).Value = c.Offset(0, -3).Value
Range("G" & (LastRow2 + n)).Value = c.Value
Range("B" & (LastRow2 + n)).Value = c.Offset(0, -8).Value
Range("P" & (LastRow2 + n)).Value = c.Offset(0, -5).Value
End If
Next c
Next n

No matter how I try I am getting this rows starting in blue color, looks like is running the loop many times and leaving me with the last line every time it runs.

this is the info I am trying to call with its information at the left as one line when the condition is met

Any help is appreciated,

Regards.

 

[My Aswer Is This]

You said:

I am trying to look at ​

one specific column​

in ​

one shee​

t of the template and when the value is > than 0 I need the value to be placed at the ​

end of a list that I have in a different sheet​

same file.
​

Why not tell us what column? You said one specific column.
And you said: in one sheet. But did not say the name of the sheet.

See there are several times here when you do not give specific sheet names and specific columns.

Please do not say read the code.

I will write the code I

 

[GWteB]

Try this:

Sub Example()

    CountResults = Application.WorksheetFunction.CountIf(Sheets("MASTER ROOMS RECONCILE").Range("L:L"), ">" & 0)
    LastRow = Sheets("MASTER ROOMS RECONCILE").Cells(Cells.Rows.Count, "C").End(xlUp).Row
    LastRow2 = Cells(Cells.Rows.Count, "I").End(xlUp).Row

    For Each c In Sheets("MASTER ROOMS RECONCILE").Range("L2:L" & LastRow)
        If c.Value > 0 Then
            LastRow2 = LastRow2 + 1
            Range("I" & (LastRow2 + n)).Value = c.Offset(0, -4).Value
            Range("J" & (LastRow2 + n)).Value = c.Offset(0, -3).Value
            Range("G" & (LastRow2 + n)).Value = c.Value
            Range("B" & (LastRow2 + n)).Value = c.Offset(0, -8).Value
            Range("P" & (LastRow2 + n)).Value = c.Offset(0, -5).Value
        End If
    Next c

End Sub

 

[walmer26]

Try this:

Sub Example()

    CountResults = Application.WorksheetFunction.CountIf(Sheets("MASTER ROOMS RECONCILE").Range("L:L"), ">" & 0)
    LastRow = Sheets("MASTER ROOMS RECONCILE").Cells(Cells.Rows.Count, "C").End(xlUp).Row
    LastRow2 = Cells(Cells.Rows.Count, "I").End(xlUp).Row

    For Each c In Sheets("MASTER ROOMS RECONCILE").Range("L2:L" & LastRow)
        If c.Value > 0 Then
            LastRow2 = LastRow2 + 1
            Range("I" & (LastRow2 + n)).Value = c.Offset(0, -4).Value
            Range("J" & (LastRow2 + n)).Value = c.Offset(0, -3).Value
            Range("G" & (LastRow2 + n)).Value = c.Value
            Range("B" & (LastRow2 + n)).Value = c.Offset(0, -8).Value
            Range("P" & (LastRow2 + n)).Value = c.Offset(0, -5).Value
        End If
    Next c

End Sub

My god! a simple line, it works. I also removed the + n as you removed one of the loops was pointless to have it there as well as the variable CountResults.

Thank you soooo much!

 

[GWteB]

You're welcome!
The CountResults = ... and + n were minor flaws, nevertheless pointless indeed.
Thanks for letting me know.