[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA For Loop not working

Posted on 2009-12-19
5
Medium Priority
?
401 Views
Last Modified: 2012-05-08
I'm new to VBA and have the attached code.  Can someone tell me what i'm doing wrong?  I'm trying to fill in cells D1-D10 with the numbers 1 through 10.
Sub AddOne()
Dim n As Integer
Dim RanngeToAdd As Range
n = 1
Set RangeToAdd = ActiveSheet.Range("e1:e10")
For Each Cell In RangeToAdd
ActiveCell.Value = n
n = n + 1
Next
End Sub

Open in new window

0
Comment
Question by:BBlu
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26087577
Hello BBlu,

SImple enough when you see it ... cell instead of activecell.  You aren't actually selecting the cell which is good therefore you need to reference 'cell'

Regards,

chris_bottomley
Sub AddOne()
Dim n As Integer
Dim RanngeToAdd As Range
n = 1
Set RangeToAdd = ActiveSheet.Range("e1:e10")
For Each Cell In RangeToAdd
Cell.Value = n
n = n + 1
Next
End Sub

Open in new window

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 200 total points
ID: 26087594
BBlu,

In fact you had a typo as well so the following tidies it up somewhat.

chris_bottomley
Sub AddOne()
Dim n As Integer
Dim RangeToAdd As Range
Dim cel As Range

    n = 1
        Set RangeToAdd = ActiveSheet.Range("e1:e10")
        For Each cel In RangeToAdd
        cel.Value = n
        n = n + 1
    Next
    
End Sub

Open in new window

0
 

Author Comment

by:BBlu
ID: 26087597
Oh....LOL.
Got it!  
Thanks, Chris.

I'm new to VBA, but having fun learning it!
0
 

Author Closing Comment

by:BBlu
ID: 31668107
Oh....LOL.
Got it!  
Thanks, Chris.

I'm new to VBA, but having fun learning it!
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26087640
We all start sometime so i'm glad to help a little bit

Chris
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question