fill down array to last available row

[mike.wilson8]

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

 

[mike.wilson8]

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]

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
(e-mail address removed)

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?

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

 

[mike.wilson8]

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]

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.

 

[mike.wilson8]

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.

 

[mike.wilson8]

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.