fill down array to last available row

[mike.wilson8@comcast.net]

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 Guillett]

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
donaldb@281.com
<mike.wilson8@comcast.net> wrote in message
news:1116451717.780640.99210@g14g2000cwa.googlegroups.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
>

[mike.wilson8@comcast.net]

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
> donaldb@281.com
> <mike.wilson8@comcast.net> wrote in message
> news:1116451717.780640.99210@g14g2000cwa.googlegroups.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 Guillett]

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=""OPEN"",2,""""))"
..Formula = .Value 'to convert from formula to just value

End With
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
<mike.wilson8@comcast.net> wrote in message
news:1116514665.003264.196840@o13g2000cwo.googlegroups.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
> > donaldb@281.com
> > <mike.wilson8@comcast.net> wrote in message
> > news:1116451717.780640.99210@g14g2000cwa.googlegroups.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
> > >
>

[mike.wilson8@comcast.net]

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 Guillett]

row 2 must be the last available row in col J.

--
Don Guillett
SalesAid Software
donaldb@281.com
<mike.wilson8@comcast.net> wrote in message
news:1116522367.257203.134720@o13g2000cwo.googlegroups.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
>

[mike.wilson8@comcast.net]

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 Guillett]

Perhaps you would like to send me a small workbook with the worksheet and
macro.

--
Don Guillett
SalesAid Software
donaldb@281.com
<mike.wilson8@comcast.net> wrote in message
news:1116522372.144203.133610@g43g2000cwa.googlegroups.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
>