my incomplete macro to fill in 8000 rows - pls help

D

dribler

Help pls.

I like to use excel03 to transpose the formulated column into a continuous
series of adjacent rows.

The formulated column of formulated data is at "e225:e263".
The rows to fill in is at
first row : L324:AX324
the next row : L325:AX325 and so on down to L8615:AX8615.

the basic trick i am thinking here is that once the data was
1. select first row "$L324:$AX324", type {=transpose($e$225:$e$263)} <c+s+e>
and copy and paste the values there, then
2. go to cell $J$324 <a formulated cell> then copy and paste the value to
cell $C$88.
Thru (2) the formulated data on column "$e$225:$e$263" shall update.
3. Then I have to repeat the process 1 & 2, wherein the next row below will
be filled with transposed-copy-value data
4. the repeated transpose-copy-value must stop only when the value of $j$324
= "END".

A macro was recorded below to fill in the first 2 rows only.
---------------------

Sub FILLIN()
' Keyboard Shortcut: Ctrl+Shift+W

Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L324:AX324").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-99]C[-7]:R[-66]C[-7])"
Range("L324").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L325:AX325").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-100]C[-7]:R[-62]C[-7])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J324").Select
End Sub
----------------
I need to possibly fill in @ 8000 rows & avoid repetitive formulas.

your encouraging help to complete the macro will suffice.

best regards,
 
J

Joel

You macro doesn't seem consistant. In on place you are transposing 30 cells
and another place you are transposing 39 cells.

I may be better if you explain how your data is placed inthe worksheet. Any
spaces between rows and columns. It is hard to see how your data is
repeating from the macro. There may be better ways of solving the problem
then the code you tried to write.
 
D

dribler

right..sorry, i recorded again and here it goes below for the unfinished
macro
-----------------
Sub FILLIN()
'
' FILLIN Macro
' Keyboard Shortcut: Ctrl+Shift+W
'
'for ROW 1
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 2
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L325:AX325").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 3
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L326:AX326").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 4
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L327:AX327").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub
-----------
i need to continue the transpose via macro in a *sequence* until such time
that J324="END"

I think i am stuck with this *so-on and so-forth* command line in vb..

thanks for trying again.
regards,
driller

Joel said:
You macro doesn't seem consistant. In on place you are transposing 30 cells
and another place you are transposing 39 cells.

I may be better if you explain how your data is placed inthe worksheet. Any
spaces between rows and columns. It is hard to see how your data is
repeating from the macro. There may be better ways of solving the problem
then the code you tried to write.

dribler said:
Help pls.

I like to use excel03 to transpose the formulated column into a continuous
series of adjacent rows.

The formulated column of formulated data is at "e225:e263".
The rows to fill in is at
first row : L324:AX324
the next row : L325:AX325 and so on down to L8615:AX8615.

the basic trick i am thinking here is that once the data was
1. select first row "$L324:$AX324", type {=transpose($e$225:$e$263)} <c+s+e>
and copy and paste the values there, then
2. go to cell $J$324 <a formulated cell> then copy and paste the value to
cell $C$88.
Thru (2) the formulated data on column "$e$225:$e$263" shall update.
3. Then I have to repeat the process 1 & 2, wherein the next row below will
be filled with transposed-copy-value data
4. the repeated transpose-copy-value must stop only when the value of $j$324
= "END".

A macro was recorded below to fill in the first 2 rows only.
---------------------

Sub FILLIN()
' Keyboard Shortcut: Ctrl+Shift+W

Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L324:AX324").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-99]C[-7]:R[-66]C[-7])"
Range("L324").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L325:AX325").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-100]C[-7]:R[-62]C[-7])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J324").Select
End Sub
----------------
I need to possibly fill in @ 8000 rows & avoid repetitive formulas.

your encouraging help to complete the macro will suffice.

best regards,
Click to expand...
Click to expand...
 
J

Joel

Your macro seem to be copying the same data over and over again instead of
moving down the worksheet

1) You code keeps on copying J324 to C88
Range("J324").Select
Selection.Copy
Range("C88").Select

2) The range E225:E263 is copyied down the worksheet to columns L - AX.
Shouldn't E225:E263 be moving down the worksheet? It look like you are
copying 39 cells. The next 39 cells does it start at E264 or is there some
rows that need to be skipped?

Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select

dribler said:
right..sorry, i recorded again and here it goes below for the unfinished
macro
-----------------
Sub FILLIN()
'
' FILLIN Macro
' Keyboard Shortcut: Ctrl+Shift+W
'
'for ROW 1
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 2
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L325:AX325").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 3
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L326:AX326").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 4
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L327:AX327").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub
-----------
i need to continue the transpose via macro in a *sequence* until such time
that J324="END"

I think i am stuck with this *so-on and so-forth* command line in vb..

thanks for trying again.
regards,
driller

Joel said:
You macro doesn't seem consistant. In on place you are transposing 30 cells
and another place you are transposing 39 cells.

I may be better if you explain how your data is placed inthe worksheet. Any
spaces between rows and columns. It is hard to see how your data is
repeating from the macro. There may be better ways of solving the problem
then the code you tried to write.

dribler said:
Help pls.

I like to use excel03 to transpose the formulated column into a continuous
series of adjacent rows.

The formulated column of formulated data is at "e225:e263".
The rows to fill in is at
first row : L324:AX324
the next row : L325:AX325 and so on down to L8615:AX8615.

the basic trick i am thinking here is that once the data was
1. select first row "$L324:$AX324", type {=transpose($e$225:$e$263)} <c+s+e>
and copy and paste the values there, then
2. go to cell $J$324 <a formulated cell> then copy and paste the value to
cell $C$88.
Thru (2) the formulated data on column "$e$225:$e$263" shall update.
3. Then I have to repeat the process 1 & 2, wherein the next row below will
be filled with transposed-copy-value data
4. the repeated transpose-copy-value must stop only when the value of $j$324
= "END".

A macro was recorded below to fill in the first 2 rows only.
---------------------

Sub FILLIN()
' Keyboard Shortcut: Ctrl+Shift+W

Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L324:AX324").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-99]C[-7]:R[-66]C[-7])"
Range("L324").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L325:AX325").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-100]C[-7]:R[-62]C[-7])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J324").Select
End Sub
----------------
I need to possibly fill in @ 8000 rows & avoid repetitive formulas.

your encouraging help to complete the macro will suffice.

best regards,
Click to expand...
Click to expand...
Click to expand...
 
D

dribler

thanks for your effort. Due to urgency, yesterday, I restarted another q.
thread "a few adjustment for a do-loop escape". If possible, please
analyze/repair/correct the short macro i had over there with the same
*target* per this thread.

Joel said:
Your macro seem to be copying the same data over and over again instead of
moving down the worksheet

1) You code keeps on copying J324 to C88
Range("J324").Select
Selection.Copy
Range("C88").Select

2) The range E225:E263 is copyied down the worksheet to columns L - AX.
Shouldn't E225:E263 be moving down the worksheet? It look like you are
copying 39 cells. The next 39 cells does it start at E264 or is there some
rows that need to be skipped?

Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select

dribler said:
right..sorry, i recorded again and here it goes below for the unfinished
macro
-----------------
Sub FILLIN()
'
' FILLIN Macro
' Keyboard Shortcut: Ctrl+Shift+W
'
'for ROW 1
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 2
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L325:AX325").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 3
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L326:AX326").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 4
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L327:AX327").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub
-----------
i need to continue the transpose via macro in a *sequence* until such time
that J324="END"

I think i am stuck with this *so-on and so-forth* command line in vb..

thanks for trying again.
regards,
driller

Joel said:
You macro doesn't seem consistant. In on place you are transposing 30 cells
and another place you are transposing 39 cells.

I may be better if you explain how your data is placed inthe worksheet. Any
spaces between rows and columns. It is hard to see how your data is
repeating from the macro. There may be better ways of solving the problem
then the code you tried to write.

:

Help pls.

I like to use excel03 to transpose the formulated column into a continuous
series of adjacent rows.

The formulated column of formulated data is at "e225:e263".
The rows to fill in is at
first row : L324:AX324
the next row : L325:AX325 and so on down to L8615:AX8615.

the basic trick i am thinking here is that once the data was
1. select first row "$L324:$AX324", type {=transpose($e$225:$e$263)} <c+s+e>
and copy and paste the values there, then
2. go to cell $J$324 <a formulated cell> then copy and paste the value to
cell $C$88.
Thru (2) the formulated data on column "$e$225:$e$263" shall update.
3. Then I have to repeat the process 1 & 2, wherein the next row below will
be filled with transposed-copy-value data
4. the repeated transpose-copy-value must stop only when the value of $j$324
= "END".

A macro was recorded below to fill in the first 2 rows only.
---------------------

Sub FILLIN()
' Keyboard Shortcut: Ctrl+Shift+W

Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L324:AX324").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-99]C[-7]:R[-66]C[-7])"
Range("L324").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L325:AX325").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-100]C[-7]:R[-62]C[-7])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J324").Select
End Sub
----------------
I need to possibly fill in @ 8000 rows & avoid repetitive formulas.

your encouraging help to complete the macro will suffice.

best regards,
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

I could not find yur new posting. will help if I understand the problem.

dribler said:
thanks for your effort. Due to urgency, yesterday, I restarted another q.
thread "a few adjustment for a do-loop escape". If possible, please
analyze/repair/correct the short macro i had over there with the same
*target* per this thread.

Joel said:
Your macro seem to be copying the same data over and over again instead of
moving down the worksheet

1) You code keeps on copying J324 to C88
Range("J324").Select
Selection.Copy
Range("C88").Select

2) The range E225:E263 is copyied down the worksheet to columns L - AX.
Shouldn't E225:E263 be moving down the worksheet? It look like you are
copying 39 cells. The next 39 cells does it start at E264 or is there some
rows that need to be skipped?

Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select

dribler said:
right..sorry, i recorded again and here it goes below for the unfinished
macro
-----------------
Sub FILLIN()
'
' FILLIN Macro
' Keyboard Shortcut: Ctrl+Shift+W
'
'for ROW 1
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L324:AX324").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 2
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L325:AX325").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 3
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L326:AX326").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'for ROW 4
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E225:E263").Select
Selection.Copy
Range("L327:AX327").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub
-----------
i need to continue the transpose via macro in a *sequence* until such time
that J324="END"

I think i am stuck with this *so-on and so-forth* command line in vb..

thanks for trying again.
regards,
driller

:

You macro doesn't seem consistant. In on place you are transposing 30 cells
and another place you are transposing 39 cells.

I may be better if you explain how your data is placed inthe worksheet. Any
spaces between rows and columns. It is hard to see how your data is
repeating from the macro. There may be better ways of solving the problem
then the code you tried to write.

:

Help pls.

I like to use excel03 to transpose the formulated column into a continuous
series of adjacent rows.

The formulated column of formulated data is at "e225:e263".
The rows to fill in is at
first row : L324:AX324
the next row : L325:AX325 and so on down to L8615:AX8615.

the basic trick i am thinking here is that once the data was
1. select first row "$L324:$AX324", type {=transpose($e$225:$e$263)} <c+s+e>
and copy and paste the values there, then
2. go to cell $J$324 <a formulated cell> then copy and paste the value to
cell $C$88.
Thru (2) the formulated data on column "$e$225:$e$263" shall update.
3. Then I have to repeat the process 1 & 2, wherein the next row below will
be filled with transposed-copy-value data
4. the repeated transpose-copy-value must stop only when the value of $j$324
= "END".

A macro was recorded below to fill in the first 2 rows only.
---------------------

Sub FILLIN()
' Keyboard Shortcut: Ctrl+Shift+W

Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L324:AX324").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-99]C[-7]:R[-66]C[-7])"
Range("L324").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J324").Select
Selection.Copy
Range("C88").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L325:AX325").Select
Application.CutCopyMode = False
Selection.FormulaArray = "=TRANSPOSE(R[-100]C[-7]:R[-62]C[-7])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J324").Select
End Sub
----------------
I need to possibly fill in @ 8000 rows & avoid repetitive formulas.

your encouraging help to complete the macro will suffice.

best regards,
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
lenghty code 4
Copy Paste Macro not Pasting Values when Blank 3
Simplify Code for Copy/Paste Special 11
Run macro from active workbook whose name keeps changing 20
Condense code 2
Something in this is causing a crash 2

Top