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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
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:
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Can you explain what you are trying to do? I don't see any mathematical relationships between the addresses in your macro above. Thanks!
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132

ADVERTISEMENT

Oh and it only took 4 hours because I was inputting formula manually! :devilish: :rolleyes: :devilish:
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Any ideas? Im thinking I might have to use integers but im not too hot on that, anyone care to teach?
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132

ADVERTISEMENT

ok bump time!

Anyone have any ideas as to how i could go about this?
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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!
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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?
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top