Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2006

Tip: Looking for answers? Try searching our database.

Looping through records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jo - 09 Sep 2006 19:54 GMT
Hi,  I am learning some basic VBA in Excel.  I know that using inout boxes
is not the best way to input multiple cells, but as I said I am playing with
VBA.

The following code takes me through cells on a single row and allows me to
enter some data.
What I cannot work out is having enjtered data into H7 , how do I then
switch to A8 and start the code again.

Would appreciate some help if someone has a few minutes.

Many thanks

Jo

CallCost = InputBox("Enter the cost of the call")
Range("A7:H7").Select
Range("A7").Select
ActiveCell.FormulaR1C1 = TelephoneNumber
Range("B7").Select
ActiveCell.FormulaR1C1 = CallDate
Range("C7").Select
ActiveCell.FormulaR1C1 = CallTime
Range("D7").Select
ActiveCell.FormulaR1C1 = Duration
Range("E7").Select
ActiveCell.FormulaR1C1 = Description
Range("F7").Select
ActiveCell.FormulaR1C1 = CallType
Range("G7").Select
ActiveCell.FormulaR1C1 = TimeBand
Range("H7").Select
ActiveCell.FormulaR1C1 = CallCost
Dave Peterson - 09 Sep 2006 21:54 GMT
One way is to find a column that will always have data in it if the row is used.

Then (in code), go to the bottom of that column and come up to that last used
row.  Then drop down one row.

I used column A in this sample:

   Dim DestCell As Range
   
   With ActiveSheet
       Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
   End With
   
   'your code for getting the values
   CallCost = InputBox("Enter the cost of the call")
   
   With DestCell
       .Value = TelephoneNumber
       .Offset(0, 1).Value = CallDate
       .Offset(0, 2).Value = CallTime
       .Offset(0, 3).Value = Duration
       .Offset(0, 4).Value = Description
       .Offset(0, 5).Value = CallType
       .Offset(0, 6).Value = TimeBand
       .Offset(0, 7).Value = CallCost
   End With

You can use that "Set DestCell..." line over and over and over (in a loop).  As
long as column A is always used.

Or you could keep track and just use it once.  Then after you fill out a row,
you drop down one row and start over.
 
   With DestCell
       .Value = TelephoneNumber
       .Offset(0, 1).Value = CallDate
       .Offset(0, 2).Value = CallTime
       .Offset(0, 3).Value = Duration
       .Offset(0, 4).Value = Description
       .Offset(0, 5).Value = CallType
       .Offset(0, 6).Value = TimeBand
       .Offset(0, 7).Value = CallCost
   End With

   Set DestCell = Destcell.offset(1,0)

It kind of depends on how you write your loop.

===========
And just as a sample, you may want to look at how Debra Dalgleish uses a
UserForm to accomplish the same kind of thing:

http://contextures.com/xlUserForm01.html

You may be surprised to see how straightforward it is.

> Hi,  I am learning some basic VBA in Excel.  I know that using inout boxes
> is not the best way to input multiple cells, but as I said I am playing with
[quoted text clipped - 29 lines]
> Range("H7").Select
> ActiveCell.FormulaR1C1 = CallCost

Signature

Dave Peterson

dbahooker@hotmail.com - 09 Sep 2006 23:06 GMT
excel doesn't house RECORDS.

records live in a database.

use the best tool for the job; and it's not Excel.

-Aaron
ADP Nationalist

> One way is to find a column that will always have data in it if the row is used.
>
[quoted text clipped - 85 lines]
> > Range("H7").Select
> > ActiveCell.FormulaR1C1 = CallCost
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.