doing a loop without "predefining" cells (ie just "B" instead of "b5")

Question

Here's my problem: I'm trying to run a loop over a specific set of data, where the amount changes with each update. If the score is >0, then it will cut/paste the specific row in columns A,B&C in the next available free row in the sheet data. This is what I have up till now:

Sub whatever()
    Dim score As Integer, sStart As Integer, sTeller As Integer, lcount As Integer, result As String

    sStart = Sheets("Packed").Range("F1").Value
    sTeller = Sheets("Packed").Range("E1").Value
    lcount = sStart
    score = Range("B& lcount").Value

    Do While lcount < sTeller
        Sheets("Packed").Select
        If score > 0 Then _ 
            Range("A&lcount:C&lcount").Select
        Selection.Cut
        Sheets("data").Select
        Range("A5").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        lcount = lcount + 1
    Loop
End Sub

What I would like is that VBA adds the "lcount" to the rowlabel and then loops it for each row in B where there is data. Thanks in advance :)

Try `Range("B" & lcount)`. If `lcount` = 5 that would evaluate to `Range("B5")` — Soulfire, May 11 '15 at 15:36
... or `Cells(lcount, "B")` or `Cells(lcount, 2)`. See [Range Object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx) and [Range.Cells Property](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx). — , May 11 '15 at 15:41

score 2 · Accepted Answer · edited May 23 '17 at 12:21

You are including too many 'pieces' when concatenating the quoted string in this code:

If score > 0 Then _ 
    Range("A&lcount:C&lcount").Select

Here are some suggestions:

If score > 0 Then _ 
    Range("A" & lcount & ":C" & lcount).Select

If score > 0 Then _ 
    Cells(lcount, "A").Resize(1, 3).Select

You may also want to review the methods detailed in How to avoid using Select in Excel VBA macros.

Vasily · Answer 2 · 2015-05-11T23:36:58.087

Loop not required in your case, see your updated code below

Sub whatever()
    Dim score&, sStart&, sTeller&, lcount&
    lcount = Sheets("data").Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Sheets("Packed")
        sStart = .Range("F1").Value
        sTeller = .Range("E1").Value
        score = .Range("B" & sStart).Value
    End With
    If score > 0 Then
        Sheets("Packed").Range("A" & sStart & ":C" & sTeller - 1).Cut
        Sheets("data").Activate: Range("A" & lcount).Insert xlDown
    End If
End Sub

doing a loop without "predefining" cells (ie just "B" instead of "b5")

2 Answers2