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

Scott Craner · Accepted Answer · 2018-05-16 18:12:03Z

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, 2018 at 18:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mathieu Guindon Over a year ago

In the recorder's defense, it uses Select and Activate specifically because it's recording user actions, because that's its very nature =)

Dar Over a year ago

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?

Scott Craner Over a year ago

On which line? @Dar

Scott Craner Over a year ago

@Dar make sure the sheet names are spelled correctly, I may have a typo there.

Dar Over a year ago

Sorry to bother but is there a way to make "for i = 4 to the last row with data in (BUILDING) column D"?

|

mer_curius · Accepted Answer · 2018-05-16 18:42:13Z

0

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

answered May 16, 2018 at 18:42

mer_curius

5846 silver badges12 bronze badges

Collectives™ on Stack Overflow

VBA Excel Loop - novice user

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related