VBA Excel Loop - novice user

Question

I am a novice at VBA but I am hoping an expert can help me with what I am trying to do.

Essentially using Record Macro and my basic grasp of VBA I have created the below; what I am trying to do is change the B4,D4 to B5,D5 B6,D6 etc... for 100 loops.

I am completely stuck and was hoping that someone could point me in the right direction.

Many many thanks.

Dar

If Sheets("BUILDING").Range("B4").Value = "" Then

Else

Sheets("CALCULATOR").Select

Range("B1").ClearContents

Sheets("BUILDING").Select

Range("D4").Select

Selection.Copy

Sheets("CALCULATOR").Select

Range("B1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Application.Run "Test1"

End If

score 2 · Accepted Answer · answered May 16 '18 at 18:12

2

The recorder is a great tool to learn how to write specific things in vba. It is bad in that it uses Select and Activate which are slow.
When only values are wanted just assign the value directly.
You loop with For, For Each, Do, ... there are others google can teach you.

Dim i as Long
for i = 4 to 104
    IF worksheets("CALCULATOR").Cells(i,"B").Value = "" then _
        worksheets("CALCULATOR").Cells(i,"B").Value = Worksheets("BUILDING").cells(i,"D").value
Next i

answered May 16 '18 at 18:12

Scott Craner

148,073
10
49
81

1

In the recorder's defense, it uses `Select` and `Activate` specifically because it's *recording* user actions, because that's its very nature =) – Mathieu Guindon May 16 '18 at 18:13
Scott, Many thanks for helping me. I have copied and pasted exactly what you have put (replacing my If to End If statement above) and when I run it I get Run-time error '9' Subscript out of range I'm not sure what that means? – Dar May 16 '18 at 19:30
On which line? @Dar – Scott Craner May 16 '18 at 19:35
@Dar make sure the sheet names are spelled correctly, I may have a typo there. – Scott Craner May 16 '18 at 19:36
Sorry to bother but is there a way to make "for i = 4 to the last row with data in (BUILDING) column D"? – Dar May 16 '18 at 20:17
That is a new question but before you ask check out this question and answers: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba . Try using that to find the last row and if you get stuck come back and ask a new question. @Dar – Scott Craner May 16 '18 at 20:49

score 0 · Answer 2 · answered May 16 '18 at 18:42

To build on Scott's answer, you could set a "range object" to a cell in your target worksheet, and offset the position of the pasted cell one click each time you loop. That gives you a little more flexibility:

dim i as long, r as range

set r = worksheets("CALCULATOR").range("B1")

for i = 0 to 99
    if worksheets("BUILDING").cells(i + 4, "B") <> "" then
        r.offset(i, 0) = worksheets("BUILDING").cells(i + 4, "D")
    end if

next i

VBA Excel Loop - novice user

2 Answers2