RE: $$Excel-Macros$$ excel vba help
Ok, thanks Paul. Dave. Date: Tue, 9 Feb 2010 20:11:23 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ excel vba help To: excel-macros@googlegroups.com 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 To: "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 To: "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 sub
Re: $$Excel-Macros$$ excel vba help
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 To: "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 To: "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 Lea
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 To: "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 emai
Re: $$Excel-Macros$$ excel vba help
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 To: "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 <><><><><><><><><><><><><><><><><><><><><><>
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 _ Video chat with Windows Live Messenger Learn how http://windowslive.ninemsn.com.au/messenger/article/870686/video-chat-with-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
Re: $$Excel-Macros$$ excel vba help
Dear Jason, To do this you do not need the macros Go to edit-> Fill-> Down That will work Sanjoy On Mon, Feb 8, 2010 at 3:24 AM, jason nix wrote: > 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 > -- Your Sincerely Sanjoy Nath Engineer EOL Gurgaon EOL/M/NZ/Engg.Dept.(Haryana Division) -- -- 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
$$Excel-Macros$$ excel vba help
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