Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown Formula
I have written this macro to copy and paste from several sheets and i works fine. However, I am having problems with the filldown formula i two seperate columns (Y5:Z5). Ideally, I would like the macro t filldown to the lastcell in the current range minus 1. The end resul should resemble the table below. column columnz Mth Stats Yr Stats Global% Global Agg% Rate Rate 48.76 101.26 4.88 65.7 60.13% 9.28% 4.42 29.5 27.00% 8.24% 4.69 53.8 49.24% 8.74% 4.77 57.9 52.99% 7.66% 5.11 98 the macro runs up to the point highlighted in yellow Can anyone help? Thanks in advance Sub CopyPaste() Application.ScreenUpdating = False Dim myfirstRow As Long, myLastRow As Long, myLastColumn As Long, n A Long, myformula As Range Set myformula = Range("Y5:Z5") n = Range("Y65536").End(x1up).Row Range("A4").Select myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row myLastColumn = Cells.Find("*", [A1], , , xlByColumns xlPrevious).Column mylastcell = Cells(myLastRow, myLastColumn).Address myrange = "A4:" & mylastcell 'myformula = "Y5:Z5:" & mylastcell Application.ScreenUpdating = True Range(myrange).Select Range(myrange).Clear 'Open Workbook Workbooks.Open Filename:= _ "C:\Pathname" Range("A10").Select myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row myLastColumn = Cells.Find("*", [A1], , , xlByColumns xlPrevious).Column mylastcell = Cells(myLastRow - 1, myLastColumn).Address myrange = "a10:" & mylastcell Application.ScreenUpdating = True Range(myrange).Select Range(myrange).Copy Windows("filename.xls").Activate Range("A4").PasteSpecial ApplicationAlert = False Columns("Y:Z").Select Application.CutCopyMode = False Selection.NumberFormat = "0.00%" Range("Y5").Select ActiveCell.FormulaR1C1 = "=RC[-23]/R4C[-23]" Range("Z5").Select ActiveCell.FormulaR1C1 = "=RC[-10]/R4C[-10]" Range("Y6").Select Range(Cell, Cells(n, cell.Column)).FillDown Windows("filename").Clos -- Carli ----------------------------------------------------------------------- Carlie's Profile: http://www.excelforum.com/member.php...fo&userid=3337 View this thread: http://www.excelforum.com/showthread.php?threadid=53436 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown Formula
Please see atachment for example table of what macro should like when run. Many thanks +-------------------------------------------------------------------+ |Filename: Doc1.doc | |Download: http://www.excelforum.com/attachment.php?postid=4661 | +-------------------------------------------------------------------+ -- Carlie ------------------------------------------------------------------------ Carlie's Profile: http://www.excelforum.com/member.php...o&userid=33377 View this thread: http://www.excelforum.com/showthread...hreadid=534365 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filldown starting from F2 instead of F1 | Excel Worksheet Functions | |||
Macro filldown | Excel Worksheet Functions | |||
Macro Filldown only with Formula | Excel Programming | |||
Macro Filldown | Excel Worksheet Functions | |||
FillDown | Excel Programming |