FormulaArray

[swaink]

Hi

I wonder of I may seek advice please on how to tweak this code.

VoG kindly provided the solution for me and it works really well. It allows me to enter formulas to the row and in each instance reflects the summary of each column.

Sub Test2()
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).Formula = "=SUM(IF(ISNUMBER(N6:N" & LR & "),N6:N" & LR & ",0)/100*$I$6:$I$" & LR & ")"
End Sub

That is it worked well until I changed the line
"Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).Formula = "

to read

"Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).FormulaArray ="

This then freezes all of the formulas entered to column N rather than change as it goes across the sheet.

Could someone advise how I can change this to reflect the column correctly

All the best

Kevin

 

[Weaver]

I suspect you'll have to address each cell individually - I've come across a similar problem myself in the past.

Would be interested to hear from other users who may know differently.

for each r in Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).cells
   r.formulaArray = "=whatever"
next r

 

[CharlesChuckieCharles]

Hi

I wonder of I may seek advice please on how to tweak this code.

VoG kindly provided the solution for me and it works really well. It allows me to enter formulas to the row and in each instance reflects the summary of each column.

Sub Test2()
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).Formula = "=SUM(IF(ISNUMBER(N6:N" & LR & "),N6:N" & LR & ",0)/100*$I$6:$I$" & LR & ")"
End Sub

That is it worked well until I changed the line
"Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).Formula = "

to read

"Range(Cells(LR + 1, 14), Cells(LR + 1, LC)).FormulaArray ="

This then freezes all of the formulas entered to column N rather than change as it goes across the sheet.

Could someone advise how I can change this to reflect the column correctly

All the best

Kevin

<B>looks like a cell referencing problem</B>


Range.FormulaArray Property

Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns null. Read/write Variant. Syntax
expression.FormulaArray
expression A variable that represents a Range object.

Remarks
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).
The FormulaArray property also has a character limit of 255.


Example
This example enters the number 3 as an array constant in cells A1:C5 on Sheet1.
<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>
<CODE>Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"</CODE></PRE></TD></TR></TBODY></TABLE>
This example enters the array formula =SUM(R1C1:R3C3) in cells E1:E3 on Sheet1.
<TABLE><TBODY><TR><TH>Visual Basic for Applications</TH></TR><TR><TD>
<CODE>Worksheets("Sheet1").Range("E1:E3").FormulaArray = _ "=Sum(R1C1:R3C3)"</CODE></PRE></TD></TR></TBODY></TABLE>

 

[CharlesChuckieCharles]

in the formula

<CODE>Worksheets("Sheet1").Range("E1:E3").FormulaArray = _ <B>"=Sum(R1C1:R3C3)"</B></CODE></PRE>

 

[swaink]

Thank you guys

Really appreciate the feedback from you

I'll go and make my changes now

Thank you both again your help is appreciated

Kevin