fill down array to last available row

[[email protected]]

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
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
> > [email protected]
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
>

[[email protected]]

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

[[email protected]]

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
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

Any ideas?

[Don Guillett]

My email address is here. Attach the file to an email to me.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> That would be great, however, I'm not sure how to send it via Google.
>
> Any ideas?
>