Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Page
I'm not sure if this can be done, but I am sure that you experts will tell me if it can't. I handle the financial affairs for a number of clients and keep my own records of their current bank balances on separate Excel Sheets within the same file. On Sheet One, I have a list of all the clients' names and I have to enter an amount of money against each client. Sheet No. 2 and onwards will represent a Bank Statement for each of the clients. Am I able to enter a figure on Sheet 1 against a particular client's name and have it automatically inputted onto their 'statement' sheet *below* the last entry that was made? The problem I'm coming up against is in making any subsequent entries appear on the statement in the *_line_below_*. (i.e. getting the script to go to the client's personal page find the last entry and drop down a line) Too complicated? Please bear in mind also that I am not an expert in VB. Many thanks. -- Fotoman ------------------------------------------------------------------------ Fotoman's Profile: http://www.excelforum.com/member.php...o&userid=30729 View this thread: http://www.excelforum.com/showthread...hreadid=503958 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Page
Quite easy, although you don't say how you associate any bank account sheet
with a client's name. So I'll give you a scenarion that you will find simple. You input sheet Column A will have the clients' names column B the amounts that you want to add column C some description column D the relevent sheet name for tha client row 1 will be the headings and that there may be blank rows between clients names We'll also assume that for each client's sheet that column A is the date yo add the payment column B is the description column C is the amount column D the total I will assume that the total on any sheet will also be the last line used copy the follwoing code to a standard module - you can then assign it to a button Option Explicit Sub Update() Dim client As Range Dim ws As Worksheet Dim sum As String Dim newrow As Long For Each client In Worksheets("sheet1").Range("A:A").SpecialCells(xlC ellTypeConstants) sum = client.Offset(, 1) 'ceck there's an amount If sum < "" Then If IsNumeric(sum) Then Set ws = Worksheets(client.Offset(, 3).Value) newrow = ws.Range("D65000").End(xlUp).Row + 1 ws.Cells(newrow, 1) = Date ws.Cells(newrow, 2) = client.Offset(, 2) 'desc ws.Cells(newrow, 3) = client.Offset(, 1) 'amount ws.Cells(newrow, 4).FormulaR1C1 = "=RC3 + R[-1]C" End If End If Next msgbox "Done" End Sub "Fotoman" wrote: I'm not sure if this can be done, but I am sure that you experts will tell me if it can't. I handle the financial affairs for a number of clients and keep my own records of their current bank balances on separate Excel Sheets within the same file. On Sheet One, I have a list of all the clients' names and I have to enter an amount of money against each client. Sheet No. 2 and onwards will represent a Bank Statement for each of the clients. Am I able to enter a figure on Sheet 1 against a particular client's name and have it automatically inputted onto their 'statement' sheet *below* the last entry that was made? The problem I'm coming up against is in making any subsequent entries appear on the statement in the *_line_below_*. (i.e. getting the script to go to the client's personal page find the last entry and drop down a line) Too complicated? Please bear in mind also that I am not an expert in VB. Many thanks. -- Fotoman ------------------------------------------------------------------------ Fotoman's Profile: http://www.excelforum.com/member.php...o&userid=30729 View this thread: http://www.excelforum.com/showthread...hreadid=503958 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i input info in excell one page at a time instead of seeing . | New Users to Excel | |||
Excel Layout two different input tables on same page | Setting up and Configuration of Excel | |||
Dates and Input Page Help!! | Excel Programming | |||
Using an input box to enter information into the header section of page setup | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |