Sometimes, Cells() is a bit easier to read.
For instance, if you want to cycle through columns A-J
and rows 1-100
and store all of the cell values in an array.
You can use:
For R = 1 to 100
For C = 1 to 10
tArray(R,C) = Cells(R,C)
Next C
Next R
I'm sure there is more that Cells() can do that isn't available in Range()
But usually, I just use the one that fits my code.
P
________________________________
From: Dave Bonallack <davebonall...@hotmail.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Tue, February 9, 2010 9:38:46 PM
Subject: RE: $$Excel-Macros$$ excel vba help
Thanks Paul,
Always learning.
BTW, does 'Cells' do anything that 'Range' can't?
Dave.
________________________________
Date: Tue, 9 Feb 2010 05:58:28 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ excel vba help
To: excel-macros@googlegroups.com
Dave...
Just a bit of 'trivia'??
you CAN use letters with Cells()
and you CAN use variables with Range()
for instance, to select Cell D10:
Ltr = "D"
C = 4
R = 10
Cells(10,4).Select
Cells(R,C).Select
Cells(R,"D").Select
Range("D10").Select
Range(Ltr & "10").Select
Range(Ltr & R).Select
all work too!!!
I use the Cells(R,"D") format frequently!
it keeps from having to insert numbers to find out what the column number is
for column "XY"
of course, Range("XY1").Column would give me the number... but still...
Paul
________________________________
From: Dave Bonallack <davebonall...@hotmail.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Mon, February 8, 2010 9:52:17 PM
Subject: RE: $$Excel-Macros$$ excel vba help
Hi Jason,
One way to increment your cell is to use the "Cells" thingy.
The "Cells" thingy uses Row, then Column within it's brackets, and it uses
numbers for both - not letters for Columns.
So, Cells(1, 3) refers to Row 1, Column 3, which is the same as Range("C3")
But with the cells thingy, you can use a variable.
So:
MyRow=15
Cells(MyRow, 1)
This refers to Row 15, Column A, and is the equivalent to Range("A15")
So you can use this sort of code:
For i = 2 to 20
(Your Copy and Paste code lines go here, using Cells(i, 1) as the paste
destination)
Next i
The "For i" code line sets the number of times the code will loop. Change this
to your requirements. Make the first number (in this example, 2) equal the your
first Row number. You can then use i as the variable in the Cells thingy.
Making the first number 2, and the last number 20, means that the code will be
repaeted 19 times.
The "Cells(i, 1)" uses the fact that the value i for starts at 2, and is
incremented every loop.
The first time, Cells(i, 1) will refer to A2. The next time through the loop, i
will be 3, so Cells(i, 1) will refer to A3, and so on.
Hope this helps.
Regards - Dave.
> Date: Sun, 7 Feb 2010 13:54:22 -0800
> From: opie...@yahoo.com
> Subject: $$Excel-Macros$$ excel vba help
> To: excel-macros@googlegroups.com
>
> I am trying to teach myself how to use the vba. I want to use a copy/paste
> and loop macro for some equations. I already know how to use my formulas to
> automatically calculate but I need help getting info into the right cell. The
> basics of it goes like this; I want to get a number to copy to the x cell and
> then copy the next number to the x cell.
> 1 x
> 2
> 3
> 4
> 5
> etc.
> I have been looking online for three days and all of the examples that I find
> do not run for one reason or another. Any plain SIMPLE explanations and
> examples would help greatly. Thanks
>
>
>
>
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,700 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
________________________________
Learn how Video chat with Windows Live Messenger
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
________________________________
If It Exists, You'll Find it on SEEK Shopping Trolley Mechanic --
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
