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

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

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

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

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

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

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

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