Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro
Hi,
I have a regular task that I do on a pivottable that looks like this (but longer): No Code Total 1 DN 841.92 PRP08 0 PRP30 0 2 RDB 0.00 RTQ 0.00 RVW 15,636.98 3 PRP42 3,435.76 Grand Total 672,328.19 I paste this info into a database which requires the number to be repeated on each line. At the moment I use a macro to copy the data down to the next entry as shown below starting in the cell that has No in: Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(-1, 0).Select Range(Selection, Selection.End(xlUp)).Select Selection.filldown and in the macro the above code is repeated 4 times and I keep repeating the macro until it gets near to the end of the list. Ideally I would like the macro to continue repeating the code above until it gets to the grand total row and then stop. I am sure this could be done using a "Do while" or similar statement. Could anyone help please? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro
try:
Select the cells in the first column, starting with the first data cell (1) down to the last required cell F5 | Special | Blanks | OK | Type = Press the up arrow | Ctrl-Enter. HTH "Michael" wrote: Hi, I have a regular task that I do on a pivottable that looks like this (but longer): No Code Total 1 DN 841.92 PRP08 0 PRP30 0 2 RDB 0.00 RTQ 0.00 RVW 15,636.98 3 PRP42 3,435.76 Grand Total 672,328.19 I paste this info into a database which requires the number to be repeated on each line. At the moment I use a macro to copy the data down to the next entry as shown below starting in the cell that has No in: Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(-1, 0).Select Range(Selection, Selection.End(xlUp)).Select Selection.filldown and in the macro the above code is repeated 4 times and I keep repeating the macro until it gets near to the end of the list. Ideally I would like the macro to continue repeating the code above until it gets to the grand total row and then stop. I am sure this could be done using a "Do while" or similar statement. Could anyone help please? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro
fantastic, thank you
"Toppers" wrote: try: Select the cells in the first column, starting with the first data cell (1) down to the last required cell F5 | Special | Blanks | OK | Type = Press the up arrow | Ctrl-Enter. HTH "Michael" wrote: Hi, I have a regular task that I do on a pivottable that looks like this (but longer): No Code Total 1 DN 841.92 PRP08 0 PRP30 0 2 RDB 0.00 RTQ 0.00 RVW 15,636.98 3 PRP42 3,435.76 Grand Total 672,328.19 I paste this info into a database which requires the number to be repeated on each line. At the moment I use a macro to copy the data down to the next entry as shown below starting in the cell that has No in: Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(-1, 0).Select Range(Selection, Selection.End(xlUp)).Select Selection.filldown and in the macro the above code is repeated 4 times and I keep repeating the macro until it gets near to the end of the list. Ideally I would like the macro to continue repeating the code above until it gets to the grand total row and then stop. I am sure this could be done using a "Do while" or similar statement. Could anyone help please? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro
Hi
I know you have an answer, but heres a bit of code, that will do what you ask: Public Sub CopyValues() For Each cell In Selection If cell.Value = Empty Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub This will require you to select the range you want changed and then run the macro. HTH. /Sune "Michael" wrote: Hi, I have a regular task that I do on a pivottable that looks like this (but longer): No Code Total 1 DN 841.92 PRP08 0 PRP30 0 2 RDB 0.00 RTQ 0.00 RVW 15,636.98 3 PRP42 3,435.76 Grand Total 672,328.19 I paste this info into a database which requires the number to be repeated on each line. At the moment I use a macro to copy the data down to the next entry as shown below starting in the cell that has No in: Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(-1, 0).Select Range(Selection, Selection.End(xlUp)).Select Selection.filldown and in the macro the above code is repeated 4 times and I keep repeating the macro until it gets near to the end of the list. Ideally I would like the macro to continue repeating the code above until it gets to the grand total row and then stop. I am sure this could be done using a "Do while" or similar statement. Could anyone help please? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) | |||
Macro - Open Word with Excel macro | Excel Discussion (Misc queries) |