fill down array to last available row

M

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
 
D

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
 
M

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?
 
D

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]
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 said:
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
Click to expand...
Click to expand...
 
M

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
 
M

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
 
M

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
 
D

Don Guillett

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