Looping through records

[Jo]

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]

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.

 

[dbahooker]

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