Automation: Adding rows and pasting values

S

SiH23

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.
 
C

Crowbar via OfficeKB.com

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?

(e-mail address removed)

www.apofficesolutions.co.uk
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.aspx/excel-programming/200902/1
 
S

SiH23

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top