Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation: Adding rows and pasting values
I would be so grateful if someone could help me to automate this process.
Cells Y2 to BK2 - alternate cells within this range contain monetary values. Cells Z2 to BL2 - alternate cells within this range contain cheque serial numbers. I need a macro to do the following: If the monetary value in £'s sterling in cell V2 is greater than £350.00, to count the number of monetary values in alternate cells between Y2 to BK2. For example: cell Y2, cell AA2, cell AC2, cell AE2 etc, etc. Add the same number of rows as the number of monetary values in alternate cells between cell Y2 to BK2 directly under row 2. Next paste all the monetary values below cell w2 (not V2) and the corresponding cheque serial number in cell X3. The corresponding cheque serial number will always be positioned to the immediate right of the cell containing the monetary value; for example if cell Y2 contains a monetary value then Z2 will contain the cheque serial number. Please could this macro run for all rows between row 2 to row 10,000. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation: Adding rows and pasting values
my business is automation of this type but I cannot visualise what you mean?
Maybe thats why no one has responded. I open to have a look at any mock up workbook you have to assist? www.apofficesolutions.co.uk SiH23 wrote: I would be so grateful if someone could help me to automate this process. Cells Y2 to BK2 - alternate cells within this range contain monetary values. Cells Z2 to BL2 - alternate cells within this range contain cheque serial numbers. I need a macro to do the following: If the monetary value in £'s sterling in cell V2 is greater than £350.00, to count the number of monetary values in alternate cells between Y2 to BK2. For example: cell Y2, cell AA2, cell AC2, cell AE2 etc, etc. Add the same number of rows as the number of monetary values in alternate cells between cell Y2 to BK2 directly under row 2. Next paste all the monetary values below cell w2 (not V2) and the corresponding cheque serial number in cell X3. The corresponding cheque serial number will always be positioned to the immediate right of the cell containing the monetary value; for example if cell Y2 contains a monetary value then Z2 will contain the cheque serial number. Please could this macro run for all rows between row 2 to row 10,000. -- Anthony Prescott http://www.apofficesolutions.co.uk Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200902/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation: Adding rows and pasting values
I shall try to send you a demo file. In the interim I shall explain it all a
bit better ;-) Column V contains a series of monetary values in £ sterling. Now, if the value exceeds £350 the payments are broken down into units of £350. For example if cell V3 contains an amount of £503.89, then cell W3 will contain a value of £350 and cell Y3 a value of £153.89 (£350+£153.98=£503.89). All the monetary breakdowns will be apparent in alternate cells from Column W up to Column CC depending on how large the value is in Column V. Located to the immediate right of the cell containing the monetary value will always be the payment reference number. Now given the example above you can see that any additional payments travel horizontally across the spreadsheet. Using the example above this is what I need to be done: The value in cell V3 is £503.89 which is broken down into two values; £350 and £153.98. The macro will then take the number of broken down values and convert into the same number of rows directly below row 3. Next it will paste £153.89 into cell w4 so it is directly under the £350 value in cell w3. The cheque serial number residing in cell z2 will then be pasted next to this amount in cell x4. Basically the paymens breakdowns (if any) will be showing travelling down the page vertically instead of horizontally. I hope this helps - please let me know if you require any further assistance. "Crowbar via OfficeKB.com" wrote: my business is automation of this type but I cannot visualise what you mean? Maybe thats why no one has responded. I open to have a look at any mock up workbook you have to assist? www.apofficesolutions.co.uk SiH23 wrote: I would be so grateful if someone could help me to automate this process. Cells Y2 to BK2 - alternate cells within this range contain monetary values. Cells Z2 to BL2 - alternate cells within this range contain cheque serial numbers. I need a macro to do the following: If the monetary value in £'s sterling in cell V2 is greater than £350.00, to count the number of monetary values in alternate cells between Y2 to BK2. For example: cell Y2, cell AA2, cell AC2, cell AE2 etc, etc. Add the same number of rows as the number of monetary values in alternate cells between cell Y2 to BK2 directly under row 2. Next paste all the monetary values below cell w2 (not V2) and the corresponding cheque serial number in cell X3. The corresponding cheque serial number will always be positioned to the immediate right of the cell containing the monetary value; for example if cell Y2 contains a monetary value then Z2 will contain the cheque serial number. Please could this macro run for all rows between row 2 to row 10,000. -- Anthony Prescott http://www.apofficesolutions.co.uk Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200902/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding values for all rows in a range | Excel Worksheet Functions | |||
Copy and pasting values of filtered rows | Excel Discussion (Misc queries) | |||
adding rows, pasting values then empty sheet | Excel Worksheet Functions | |||
Adding validation fails with automation error | Excel Programming | |||
pasting a formula without so much automation | Excel Programming |