| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need better macro
I so seldom have to edit my macros anymore, since most have worked now
for several years with little trouble. However, the macro below fills in blank cells that have a different number of rows each month. Until this month, Range commands all began at A11, which worked fine until I had to insert three rows above it. I didn't think of the impact and of course the macro referencing cells in row 11 caused this macro to really make quite a mess. Below, there are different sets of copy opearations that take place based on certain colums, and that shouldn't change. However for anything below that now references row 14, how can I defend the integrity of the macro if I need to insert or delete rows in the future? There is no range name defined right now that begins on row 14, but there is a row 11 range name called START, if you wanted to offset it that way like I did it on the 3rd line of the macro below. I appreciate the help. I just do these every day anymore to remember all the tricks available. Bruce Sub Fillinblanks() Dim cRows As Long cRows = Cells(14, 7).End(xlDown).Row Range("Start").Offset(3, 0).Select Range(ActiveCell, ActiveCell.Offset(0, 5)).Select Range("a14:F14").AutoFill Destination:=Range("A14:F14", Cells(cRows, "A")) Range("A14:F14", Cells(cRows, "A")).Copy Range("A14:F14", Cells(cRows, "A")).PasteSpecial Paste:=xlValues Range("H14:I14").AutoFill Destination:=Range("H14:I14", Cells(cRows, "H")) Range("H14:I14", Cells(cRows, "H")).Copy Range("H14:I14", Cells(cRows, "H")).PasteSpecial Paste:=xlValues Range("L14:O14").AutoFill Destination:=Range("L14:O14", Cells(cRows, "L")) Range("L14:O14", Cells(cRows, "L")).Copy Range("L14:O14", Cells(cRows, "L")).PasteSpecial Paste:=xlValues Range("P14").Copy Range("P15").Select Range(Selection, Selection.Offset(cRows - 14, 0)).PasteSpecial Range("R14:AV14").AutoFill Destination:=Range("R14:AV14", Cells(cRows, "R")) Range("R14:AV14", Cells(cRows, "R")).Copy Range("R14:AV14", Cells(cRows, "R")).PasteSpecial Paste:=xlValues Range("K14").Copy Range("K15").Select Range(Selection, Selection.Offset(cRows - 11, 0)).PasteSpecial Application.CutCopyMode = False Range("A1").Offset(cRows, 0).Select Range("LuseEntry").Copy ActiveCell.PasteSpecial xlPasteValues End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need better macro
Can you give us some sample data that we can try it on?
-- __________________________________ HTH Bob wrote in message ... I so seldom have to edit my macros anymore, since most have worked now for several years with little trouble. However, the macro below fills in blank cells that have a different number of rows each month. Until this month, Range commands all began at A11, which worked fine until I had to insert three rows above it. I didn't think of the impact and of course the macro referencing cells in row 11 caused this macro to really make quite a mess. Below, there are different sets of copy opearations that take place based on certain colums, and that shouldn't change. However for anything below that now references row 14, how can I defend the integrity of the macro if I need to insert or delete rows in the future? There is no range name defined right now that begins on row 14, but there is a row 11 range name called START, if you wanted to offset it that way like I did it on the 3rd line of the macro below. I appreciate the help. I just do these every day anymore to remember all the tricks available. Bruce Sub Fillinblanks() Dim cRows As Long cRows = Cells(14, 7).End(xlDown).Row Range("Start").Offset(3, 0).Select Range(ActiveCell, ActiveCell.Offset(0, 5)).Select Range("a14:F14").AutoFill Destination:=Range("A14:F14", Cells(cRows, "A")) Range("A14:F14", Cells(cRows, "A")).Copy Range("A14:F14", Cells(cRows, "A")).PasteSpecial Paste:=xlValues Range("H14:I14").AutoFill Destination:=Range("H14:I14", Cells(cRows, "H")) Range("H14:I14", Cells(cRows, "H")).Copy Range("H14:I14", Cells(cRows, "H")).PasteSpecial Paste:=xlValues Range("L14:O14").AutoFill Destination:=Range("L14:O14", Cells(cRows, "L")) Range("L14:O14", Cells(cRows, "L")).Copy Range("L14:O14", Cells(cRows, "L")).PasteSpecial Paste:=xlValues Range("P14").Copy Range("P15").Select Range(Selection, Selection.Offset(cRows - 14, 0)).PasteSpecial Range("R14:AV14").AutoFill Destination:=Range("R14:AV14", Cells(cRows, "R")) Range("R14:AV14", Cells(cRows, "R")).Copy Range("R14:AV14", Cells(cRows, "R")).PasteSpecial Paste:=xlValues Range("K14").Copy Range("K15").Select Range(Selection, Selection.Offset(cRows - 11, 0)).PasteSpecial Application.CutCopyMode = False Range("A1").Offset(cRows, 0).Select Range("LuseEntry").Copy ActiveCell.PasteSpecial xlPasteValues End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need better macro
On Dec 12, 10:58*am, "Bob Phillips" wrote:
Can you give us some sample data that we can try it on? Ok, Bob, here is some data, since I don't know how I can attach a spreadsheet in this group within the google groups interface. 1 182 470 0 51079000 51079000-30 001 001 1 182 470 0 10104000 10104000-30 001 001 Explanaton: Columns A to F in this case extend through the "0" values just after the "470"; meaning there are two columns with no values in them in this case. Columns A to F are copied down as far as the number of rows in crows. H and I are blank data fields, but populted with ' ' in them just B and C are. J is populated earlier through a separate function, so its here just so you can see the variation in types of data. Some fields are formatted numerically, and others are formatted with characters. I had a problem with the columns K and L on the autofill because 001 is a constant and I can't have it increment by 1 each row. That is why column L, the 2nd 001 just got changed to be like the copy opearation shown in this macro in column K, so it won't increment by 1 like it was doing. I could of attached the whole spreadsheet to make it easier to play with, but I didn't know how to do it in this interface. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
| Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
| Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Linear Mode
