Looping through records

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 


0
Jo4895 (1)
9/9/2006 6:54:24 PM
excel 39879 articles. 2 followers. Follow

2 Replies
417 Views

Similar Articles

[PageSpeed] 40

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.

Jo wrote:
> 
> 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
0
petersod (12005)
9/9/2006 8:54:27 PM
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



Dave Peterson wrote:
> 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.
>
> Jo wrote:
> >
> > 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

0
dbahooker (137)
9/9/2006 10:06:57 PM
Reply:

Similar Artilces:

Object Name and For Loop
Good morning, It has probably been asked before and it is definitely a beginner's question, but any response it greatly appreciated. I have a series of label boxes on a form and I need to set the caption on each of them equal to a cell in the spreadsheet. I know how to do this the long way: label1.caption = cells(1,1) but I was wondering if I could use a for loop and make this process a little easier for the future... something like: for i = 1 to 14 dim lbltest as object set lbltest = "userform1.label" & i lbltest.caption = cell(i +1,1) next any ideas? I found ...

how is a tabbed form updated when another form alters the records
how is a tabbed form updated when another form alters the recordset - without closing and re-opening the forms It depends on how the form's set up. If the various tabs simply refer to fields in the form's RecordSource, you can requery the form: Me.Requery. If the tabs are actually subforms, you'd have to requery the appropriate tabs: Me!NameOfSubformControl.Form.Requery Note that depending on how the subform is added, the name of the subform control may be different than the name of the form being used as a subform. -- Doug Steele, Microsoft Access MVP http://I.Am/DougS...

Using filtered form data as record source for report
I have a split form in an Access 2007 database where the data grid shows the records from a query. The user can filter any combination of fields from the the drop down headers. I have a command button above the data grid that opens a report with the same query as its record source. I'm trying to figure out how to pass the filtered data to the report so it will show only those records that the user is seeing in the data grid of the split form. Any help with this issue will be greatly appreciated. Ken You may be able to pass the Filter of your form as the WhereCondition ...

Select Records for Subtotals Based on a Given Condition
I have a LONG list of jobs that are sort alphabetically by customer name in one worksheet. I would like to create another worksheet that can go through the first worksheet ('KEN_S_QUERY') and select the rows where in a certain column there is a certain customer, and total up the dollar amount in the column next to the customer name. I am very familiar with access, which makes it harder to word my question here. I would use access, but I need the graphing a charting functions of excel in this case. Is there a way (perhaps through SQL? please dear god, SQL.) to select certain ...

Sequential numbering of records
Having read the various postings on the subject i am more confused than ever. what I want to do is to create a field that will contain sequential numbers starting with an initial number of say 20000 and incrementing by 1 such that record 1 is 20000, record 2 is 20001, record 3 is 20003 etc as each record is entered using an input form. Something similar perhaps to the series fill in Excel or a customisable autonumber field. my question is what is the simplest way of doing this for what would seem to be a fairly common problem in the Form_Current Sub Me.CustomNumberField.DefaultValue =...

Recording a new loan in my checkbook ledger
This is a very simple question. I've got Microsoft Money Plus for 2008. I have gotten a new loan and it was deposited into my checking account. The loan is from the same bank as my checking account. How do I record that in MS Money? Do I just put in my bank's name, or what? I know this question is very simplistic, but what I'm concerned about is not confusing myself, at least, with what I see in MS Money when I review the ledger there. Rod It's not clear what part of this activity you want to record in your checkbook ledger. The receipt/deposit of the loan proce...

Convert From Column to Row (Record)
I have a table: ID Price Quantitiy 1 1.00 2 I would like to create a make table query to convert the original table to something like this: ID Measure Data 1 Price 1.00 1 Quantity 2 Please help, hopefully ther is a simple solution!!! Look up Excel Paste Special - Transpose for a solution. Or try this --- SELECT "Price" AS Measure, [URTable].[Price] AS Data FROM URTable UNION ALL SELECT "Quantity" AS Measure, [URTable].[Quantity] AS Data FROM URTable; -- KARL DEWEY Build a little - Test a little "banker123&qu...

multiple duplication of records
Could anyone assist me with the following:- I have been using the quick reference search tool that can be found at www.access-programmers.co.uk/forums/showthread.php?t=120366 <http://www.access-programmers.co.uk/forums/showthread.php?t=120366> (cool search tool.zip) Using only one table with no problems however I am now trying to use it in a relationship configuration Two tables linked back to the main table via one too many links. What is now happening is that multiple duplication of records are being displayed. An example If you have one table with two records the listbo...

go to record of a form from a subform
hi i have a form in data entry mode and a subform showing the related records to the form. i want to double click on the a specfic record in the subform the shows me the details in the main form. what is the best way in doing it. thanks I don't understand this question. The normal way you set up a form with a subform is to match a one-to-many relationship. The table on the ONE side of the relationship is the source for the main form. The table on the MANY side is the source for the subform. If you set up your form like that, you only see the related records in the subform if you alre...

Increasing number of records in a work book
I want to view an excel sheet with >500000 records, created from an editor in csv format (comma separated values). I can view only 65536 records. How can I increase the number of records? M E Ramakrishnan Hi one Excel worksheet has a maximum of 65536 rows. There is no way around this. You can try one of the following: - split your csv file on different worksheets - I'd recommend using a database for viewing this amount of data (e.g. MS Access) -- Regards Frank Kabel Frankfurt, Germany M E Ramakrishnan wrote: > I want to view an excel sheet with >500000 records, > created ...

Add Record with Refresh
I have a form that is linked to a parameter query. It works great to add one record. When the user opens the form is asks for the ID and then the data is enter, the query links two tables (one to many relationship). However, the issue is that if they need to enter information for another ID, the form does not ask the paramater information when they hit new record. So they have to close the form and open the form to enter the new parameter information. How do I set it up for the form to ask for the paramaters each time a new record is added? -- Message posted via AccessMonster.com http:/...

Creating a customer record in a child table
A big thank you again for the help I have been given so far. I want to create a child record linking to the original customer. I thought this would be simple [call]![customer_ID].value = [customer]![ID].value [customer]![ID].value gives me the ID of the first record in the table, not the currently selected record. What is the easy way of doing this? On Fri, 26 Mar 2010 04:40:56 +1100, Avid Fan <me@privacy.net> wrote: >A big thank you again for the help I have been given so far. > >I want to create a child record linking to the original customer. > &g...

Recording Cash
I'm new to Money and can't figure out how to account for cash purchases. I'm trying to keep track of cash expenditures within a budget. Hi Dave, I'm not sure how everyone else does it but when I do an ATM withdrawal, I post it to a category called 'Cash Withdrawal', then when I accumulate receipts for items I pay cash for I go to 'Categories' and double click on 'Cash Withdrawals', all of the transactions are listed there. I then double click on one of them and change the category for a transaction to the appropriate category, depending on the...

Summing up records in a Form
(Sorry for cross-posting. I accidentally posted to the "General Questions" track, but it fits obviously better here:) I have a table with four fields: (1) a unique ID, (2) a filter field, (3) a text (not unique) and (4) a number. In my form I want to filter the table by a combox selection (in this case the value "f1") and collapse the remaining records with identical text and show the sum of the number. Records (myId, myFilter, myTxt, myNum): 1, f1, texta, 9 2, f1, texta, 8 3, f2, texta, 7 4, f1, textb, 6 .... I want to filter only the "f1" records and see:...

How do I record a transaction to Accounts receivable?
I bought an item for "John" that cost $300. He will pay me back later. I paid for the item from my bank account and record it in Money as an expense. I'd also like to show the transaction as an accounts receivable from "John" or a loan to "John". How can I accomplish this? Craig, Create a new asset account called "Accounts Receivable" and record it in your bank account as a transfer to that account. This will result in the creation of a debt of $300. when the cash is paid back do a similar transfer. "Craig" wrote...

Record view priviledge based on data element
Hi Team, Is there any way we can restrict particular record based on the data element? I mean to say that if I have a value in the record like 'IC Client', this records should be viewable by a particular security group only. In my case this security group is a BU as well. Thanks in advance. This is not possbile "out of the box" right now. Maybe future release will support "field level" security role settings... Good luck. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Dave Banerjee" wrote: > Hi Team, > &...

Adding columns to Look Up Records window #2
In certain places in CRM (specifically highlight a Lead, click New Email, and press the looking glass lookup button to the right of the To field) when I do a Lookup for contacts or accounts, the window Look Up Records appears with a list of entities at the top, a search field, and two lists below. The left list is available records and the right is selected records. The Available Records list only displays a single column, the name of the account or contact. I want to display more columns, and scollbars are OK. My specific issue is that I have 4 Rick Johnson contacts in my database, ...

NotInList trouble displaying new record
i am using the following code to create a new entry in tblRoomName when an entry in my combobox is different than the listed entries: Private Sub Combo22_NotInList(NewData As String, Response As Integer) Set dbs = CurrentDb strAuth = "'" & NewData & "' is not in the list. " strAuth = strAuth & "Would you like to add it?" If MsgBox(strAuth, vbYesNo + vbQuestion, "New Group Diagnosis") = vbNo Then Response = acDataErrDisplay Else Set rst = dbs.OpenRecordset("tblRoomName") rst.AddNew rst![RoomType] = NewData rst.Update R...

How do I prevent macro from looping
I want to trigger a macro called "AddC" whenever I enter data into cell and press enter. I have the following code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Address = "$D$22" Then AddC End If End Sub the code for Addc is: Range("W22").Select Selection.Copy Range("D22").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("D23").Select End Sub When it fires it goes into a continual loop. Why ...

How do you automatically record a revision date
Hi, Whenever a worksheet is revised I need to automatically record a new revision date. The next time the worksheet is edited a new date automatically replaces the old date. Thanks for the help, jerry And that's a bad thing?? I don't get the picture. Ideally what you want is a column of revision dates?? It seems that what you want to do is to show the most recent revision date if it's recorded in only one place. What good is an old revision date. As you can see I just don't understand. >-----Original Message----- >Hi, >Whenever a worksheet is revised I need to a...

looping
hi. i am trying to loop through the #DIST table for however many track_dates are in the #DATES table, assigning the #DATES.currentrow's track_date to each record. just not sure how to do this. here is my code: DECLARE @intFlag int DECLARE @intCount int SET @intCount = (SELECT COUNT(*) FROM #DATES) SET @intFlag = 1 WHILE (@intFlag < (@intCount + 1)) BEGIN INSERT into #TEST SELECT one, two, three from #TEST SET @intFlag = @intFlag + 1 END thanks in advance, geebee > i am trying to loop through the #DIST table for however many track_dates > a...

Display unique record
I have two tables, Client Info and Client Visits. The first table has general info that does not change so Client info is enter one time only. The second table records services provided at each client visit. The first table has Client ID and the second has Visit ID and Client ID. During the month a client may have several visits. Therefore the Client ID would occur several times while each visit has a unique Visit ID. For a given month I want to know how many different clients were seen as well as count the fields of info. associated with them. At present I am able to get a total...

Sharing Records
Hi Here's a strange issue that keeps happening and I simply can't explain The Scenario: ----------------- - MS-CRM 4.0 Professional - User A belongs to BU.1 - User B belongs to BU.2 (different branchs) - Both users have the lowest security role level (CRUD operations allowed only for user owned records) The Test: ----------- - User A creates a Quote - User A Shares the Quote with user B giving Read permission only The Mystery: --------------- - User B opens the Quote - User B overrides Quote Name - User B is able to Save Quote changes with no warning message Does this happen to ...

Add New Record / Make Existing Records Read Only
I would like open a form to add a new record and let the user browse existing records without being able to modify them. How can I accomplish this? Thanks On Sun, 7 Mar 2010 18:37:02 -0800, Ron <Ron@discussions.microsoft.com> wrote: >I would like open a form to add a new record and let the user browse existing >records without being able to modify them. How can I accomplish this? >Thanks Set the Form's Allow Additions property to Yes, and its Allow Edits property to No. -- John W. Vinson [MVP] Ron wrote: > I would like open a form to ad...

how can i loop a macro
this is my example dry = new rate for dryers dry#### = old rate for dryers ____________________ dry1052 | 50 | _______|____________| dry | 50 | _______|____________| dry1152 | 40 | _______|____________| dry | 40 | _______|____________| this continues.....for about 1500 cells i want a macro that will combine information, (=a2&""&b2) and makes part of it, but i also need to get last 2 letters in colum A, onto the combined cell. i can do everything but get last 2 letters and make a macr...