Reproduce formula in sequence

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
I am trying to reproduce formulae over a couple thousand rows, unfortunately there are gaps but the formulae must remain sequential,
this is the basic recorded macro to cut copy the data as it is needed but i would like a macro that can start from any active cell (being the top left cell of the first range) as i have to repeat this procedure over several sheets (first sheet took 4 hours!)
Tried using integers but got myself in a kerfuffle! :oops:
Help me! :cry:

Code:
Sub Macro1()
    Range("A13:M29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O21").Select
    Application.CutCopyMode = False
    Range("O21:AA37").Cut Destination:=Range("O50:AA66")
    Range("O50:AA66").Select
    Selection.Copy
    Range("A50").Select
    ActiveSheet.Paste
End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Jamie

I think we need more information.:)

It's hard to work out what you actually want to do from that code snippet.

And anything that takes 4 hours, well that just doesn't make sense.:eek:
 
Upvote 0
Can you explain what you are trying to do? I don't see any mathematical relationships between the addresses in your macro above. Thanks!
 
Upvote 0
Sorry i shouldve made notations, (the notated ranges in text are reference to code)

Basically I have formula in the 'First Range' taking data from another sheet, the 'First Range' contains 16 rows, the data those rows contain come from 8 rows on another sheet (data is concatenated and moved to where it should be so is required to take up 2 rows per original row)

The 'Paste Range' moves the data into columns that do not affect the printable area of the and moves the rows down by 8 so that the formula then follow on directly after the 'First Range' ie. if the first cell in the 'First Range' was ='Sheet2'!A1 then through copying it would become ='Sheet2'!O9. not the final result I need but it adjusts the rows correctly.

When Cutting the formulae do not change as per their new position so that the 'Cut Range' does not change the formulae but aligns the new formulae with the final destination rows. They still have wrong columns though and this is where the 'Final Range' comes in, it copies back to original columns and new rows so that the 'Final Range' has formulae carrying on directly from the 'First Range'

Thinking about it the areas outside the print area would need to be cleared of borders, colours and data after the process.
Hope ive explained it clearly now :biggrin:

Code:
Sub Macro1() 
'First Range
    Range("A13:M29").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
'Paste Range
    Range("O21").Select 
    Application.CutCopyMode = False
'Cut Range 
    Range("O21:AA37").Cut Destination:=Range("O50:AA66")
    Range("O50:AA66").Select 
    Selection.Copy 
'Final Range 
    Range("A50").Select 
    ActiveSheet.Paste 
End Sub
 
Upvote 0
Any ideas? Im thinking I might have to use integers but im not too hot on that, anyone care to teach?
 
Upvote 0
Still not sure what exactly you are trying to do? Are you trying to create formulas in Sheet2 that point back into Sheet1 only split up into 2 rows in Sheet2? Maybe a small subset of actual formulas and how they neee to end up would be helpful.

In trying to work through your example above, it appears you are just moving a block of data outside a print area and then moving it back but further down on the sheet. Since you set CutCopyMode false before you do anything else in your 'Paste' block it doesn't look to me like anything happens there. Sorry to be so obtuse, but I'm having a hard time getting my arms around this. Good Luck!
 
Upvote 0
ok lets say sheet1 contains rows of data, sheet2 has formula pointing at this data eg ='sheet1'!A1.

In sheet2 the data is split between two rows so that in rows 3&4 it has data from sheet1 row 1 and then in 5&6 data from row 2 from sheet1.

This carries on over 8 sets of data (16 rows) then is a gap in the rows for other information. 21 rows between the last row of formula and the first row of the formula to be created. So if the last row of formula was looking at row 8 of sheet 1 then the new row of formula (21 rows below) should be aiming at row 9 of sheet one.

Am I making any sense at all yet?
 
Upvote 0
I think I've got it.

1. Sheet2 data points back into Sheet1 data via formulas
2. Sheet2 data splits Sheet1 data such that each row from Sheet1 is split into 2 rows in Sheet2
3. Sheet2.Row3 is the initial starting point and points to Sheet1.Row1
3. Sheet1 data is processed in sets of 8 rows
4. Other stuff happens in Sheet2
5. The second set of Sheet2 data begins at Sheet2.Row21 and points to Sheet1.Row9

As an example,
1. Starting at Row 3 in Sheet2, formulas in Sheet2 point back into Sheet1.Row1. Each row in Sheet1 ends up being split into 2 rows in Sheet2, albeit in a consistent manner.
2. Rows 3 and 4 in Sheet2 have formulas pointing back to Sheet1.Row1
3. Rows 5 and 6 in Sheet2 have formulas pointing back to Sheet2.Row2, etc for 8 sets of data ...
4. Rows 17 and 18 in Sheet2 have formulas pointing back to Sheet1.Row8

Assuming the above is basically correct,

1. What is the first row in Sheet2 which points back to Sheet1.Row1
2. What column in Sheet2 points to Sheet1.A1
3. How many data columns exist in Sheet1
4. What Sheet1 data column corresponds to the first Sheet2 second row
5. Is Sheet1 data guaranteed to be in sets of 8 or could you end up with 19 rows or something odd like that
6. What is the Sheet2 row where Sheet1.Row9 should be mapped

I think we are almost there. Good Luck!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top