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

Answer 1

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

Answer 2

1. 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.

2. When only values are wanted just assign the value directly.

3. 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