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

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

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

I suspect that this is what you want.

Sub placearrayformulae1()
Set jrng = Range("j2:j" & Cells(Rows.Count, "J").End(xlUp).Row)
With jrng
..Formula = "=IF(OR(I2=""CLOSED"",I2=""resolved""),1,IF(I2=""O PEN"",2,""""))"
..Formula = .Value 'to convert from formula to just value

End With
End Sub

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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

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

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

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

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

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

That would be great, however, I'm not sure how to send it via Google.

Any ideas?

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?