Home |
Search |
Today's Posts |
#1
|
|||
|
|||
fill down array to last available row
I'm trying to build a macro that will fill down an array formula from
J2 to the last available row in column J. Example... Range("J2").Select Selection.FormulaArray = _"my array formula" Range("J2:to last row in Column J").Select Selection.FillDown Any ideas? Thank you |
#2
|
|||
|
|||
try this
Sub placearrayformulae() Set frng = Range("j2:j" & Cells(Rows.Count, "J").End(xlUp).Row) With frng ..FormulaArray = "=yourarray" ..Formula = .Value'to convert from formula to just value End With End Sub -- Don Guillett SalesAid Software wrote in message ups.com... I'm trying to build a macro that will fill down an array formula from J2 to the last available row in column J. Example... Range("J2").Select Selection.FormulaArray = _"my array formula" Range("J2:to last row in Column J").Select Selection.FillDown Any ideas? Thank you |
#3
|
|||
|
|||
Don,
Thanks for the code below and I think we're close, but when I run the macro, it just writes "0" value to row 1 and 2 in column J. Here's my code: Sub placearrayformulae() ' ' Macro3 Macro ' Macro recorded 5/19/2005 by Mike Wilson Set frng = Range("j2:j" & Cells(Rows.Count, "J").End(xlUp).Row) With frng ..FormulaArray = "=IF(RC[-1]=""CLOSED"",1, IF(RC[-1]=""OPEN"",2, IF(RC[-1]=""RESOLVED"",1,)))" ..Formula = .Value 'to convert from formula to just value End With End Sub Any ideas? Don Guillett wrote: try this Sub placearrayformulae() Set frng = Range("j2:j" & Cells(Rows.Count, "J").End(xlUp).Row) With frng .FormulaArray = "=yourarray" .Formula = .Value'to convert from formula to just value End With End Sub -- Don Guillett SalesAid Software wrote in message ups.com... I'm trying to build a macro that will fill down an array formula from J2 to the last available row in column J. Example... Range("J2").Select Selection.FormulaArray = _"my array formula" Range("J2:to last row in Column J").Select Selection.FillDown Any ideas? Thank you |
#5
|
|||
|
|||
Don,
When I run the macro, somethings going on with the range. It's writing values to row 1 and 2 only in column J. Any ideas on how to chg the Set jrng for it to write J2 down to last available row? Thanks for your help? Mike |
#6
|
|||
|
|||
Don,
When I run the macro, somethings going on with the range. It's writing values to row 1 and 2 only in column J. Any ideas on how to chg the Set jrng for it to write J2 down to last available row? Thanks for your help? Mike |
#7
|
|||
|
|||
row 2 must be the last available row in col J.
-- Don Guillett SalesAid Software wrote in message oups.com... Don, When I run the macro, somethings going on with the range. It's writing values to row 1 and 2 only in column J. Any ideas on how to chg the Set jrng for it to write J2 down to last available row? Thanks for your help? Mike |
#8
|
|||
|
|||
I wish it was that easy, but col J has over a thousand rows. Also it
doesn't seem to make sense that the macro is also writing a value to row 1, when the range in the code looks like it starts at row 2. Not sure? If you can think of anything else, please let me know. Thanks, Mike |
#9
|
|||
|
|||
Perhaps you would like to send me a small workbook with the worksheet and
macro. -- Don Guillett SalesAid Software wrote in message oups.com... Don, When I run the macro, somethings going on with the range. It's writing values to row 1 and 2 only in column J. Any ideas on how to chg the Set jrng for it to write J2 down to last available row? Thanks for your help? Mike |
#11
|
|||
|
|||
My email address is here. Attach the file to an email to me.
-- Don Guillett SalesAid Software wrote in message oups.com... That would be great, however, I'm not sure how to send it via Google. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
fill series grayed out (not available, disactivated) | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |