Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
Hi I am new to macros, and havent any past experience with programming. I am looking for a way of making a macro that looks for the last row in a table of three columns, and fills it down a row. A rough idea of what i want can be found here 'Macro Question' (http://www.yakery.com/images/macrohelp.png) Thanks in advance, ben -- benb7760 ------------------------------------------------------------------------ benb7760's Profile: http://www.excelforum.com/member.php...o&userid=24382 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
Give this a try Code: -------------------- Sub FillMacro() Dim NumRows As Long NumRows = ActiveSheet.UsedRange.Rows.Count Range("A" & NumRows & ":C" & NumRows).AutoFill Destination:=Range("A" & NumRows & ":C" & NumRows + 1), Type:=xlFillDefault End Sub -------------------- I'm not sure what you mean by "It would be good if it could auto put in another value from another reference" Do you want one of the cells Ax, Bx or Cx to have a fixed value? or depending on what row you are filling into it will pull a value from somewhere else on the same worksheet or another worksheet in the same workbook, etc. What value do you want it to put in? -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
To insert a value into the b and c columns from another cell reference it would be good if this can be achieved, but it isnt necessary I have updated the situation slightly, and here is anothe demonstration: 'Macro Help (http://www.yakery.com/images/macrohelp2.png) The last that you sent me appears to be adding rows, but I'm not sur what it is filling; the rows are blank : -- benb776 ----------------------------------------------------------------------- benb7760's Profile: http://www.excelforum.com/member.php...fo&userid=2438 View this thread: http://www.excelforum.com/showthread.php?threadid=37971 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
The macro I sent you will find the last used row on your sheet and then fill down one row from there in columns A, B, and C. Since your column A has the Month-Year then it will increment that value. It appears that your column B and C have numeric values so it will simply copy that same value into the next row. Do you want the value that is currently in the Overview section of your spreadsheet to be filled into column B and C? If that is the case try this. Code: -------------------- Sub FillMacro() Dim NumRows As Long NumRows = ActiveSheet.UsedRange.Rows.Count Range("B" & NumRows & ":B" & NumRows).AutoFill Destination:=Range("B" & NumRows & ":B" & NumRows + 1), Type:=xlFillDefault Range("C" & NumRows+1) = Range("F6") Range("D" & NumRows+1) = Range("F7") End Sub -------------------- I have modified the positions to reflect the positions of your two tables in the MacroHelp example you show. HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
That works exactly how i want it, there is one slight problem, that it starts filling from row 50, when the current row it should start from is 16, and because its starting all the way down there, it is not filling the date because it isnt in the cell above it. Any way to get it to start filling from row 16, where it needs to ? If you dont understand: 'Screenshot and annotation' (http://www.yakery.com/images/macrohelp3.png) Thanks for all the help so far! -- benb7760 ------------------------------------------------------------------------ benb7760's Profile: http://www.excelforum.com/member.php...o&userid=24382 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
benb7760, That is one drawback with the using the UsedRange command the wa I have in this macro. If you have ever done something in a row below the bottom of your tabl (lower than row 15 in this case) it will find that wit UsedRange.Rows.Count. A quick and easy work around is to highlight all rows (or at leas several hundred) starting at the bottom of your table (row 16) the right clicking and select 'delete' (Don't just use the delete keyboar button). This will clear any reminants of activitiy on the lower rows of you sheet then the macro will run correctly. I have seen posts which suggest a more robust way to find the las populated row in the sheet and will check if I can find tha information -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37971 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
thank you, everything is working fine. If you do find a better way to find the last row, it'd be great if you could show me how to work it into the formula. The only problem left now is that the cells that you showed me how to do automatically: Range("C" & NumRows + 1) = Range("F6") Range("D" & NumRows + 1) = Range("F7") Arent getting the cell formatting that i desire. Is there a way to do this? Thanks for all your help! -- benb7760 ------------------------------------------------------------------------ benb7760's Profile: http://www.excelforum.com/member.php...o&userid=24382 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Macro
If you are always going to be working on the same sheet then the easiest way to do it would be to preformat the cells with the desired format. You can also do it programically by adding the following lines Range("C" & NumRows + 1) = Range("F6") Range("C" & NumRows + 1).number.format = "$#0.00" Range("D" & NumRows + 1) = Range("F7") Range("D" & NumRows + 1).number.format = "$#0.00" If you want a different number format then I'd suggest recording a macro and manually changing the numberformat of a number of different cells and that will give you an idea of how the syntax will look for various types of numberformats. To count the number of rows in a specific column you can replace the NumRows line with the following: NumRows = Cells(Rows.Count, "B").End(xlUp).Row This assumes your Dates are in Column B as in your example. HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379710 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Fill | Excel Worksheet Functions | |||
Fill Macro II | Excel Worksheet Functions | |||
Fill Down Macro | Excel Discussion (Misc queries) | |||
using a macro for a fill | Excel Programming | |||
Fill Macro | Excel Programming |