PDA

View Full Version : Solved: Implement formula as value in event code



Shazam
07-05-2006, 09:41 AM
Hi everyone,


I would like to implement this formula below as a value by using a worksheet event code.




ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=1,Range_L)))),AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=2,Range_L)))))"

I tried to declared as Formula Array range but no luck.

Any Ideas?





Private Sub Worksheet_Change(ByVal Target As Range)


'Check validity

Dim FormulaArray As Range


If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then Exit Sub
On Error Goto GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)
For Each c In Cols
Target.Offset(0, c).FillDown


ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=1,Range_L)))),AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=2,Range_L)))))"


.Value = .Value
Next
GetOut:
Application.EnableEvents = True
End Sub

OBP
07-05-2006, 09:45 AM
I would break it down in to discreet VBA lines of code for the If/then statemants instead of using like a worksheet Formula.
You can then check each line, get it working before moving on to the next.

Shazam
07-05-2006, 09:51 AM
The worksheet event code is inputing the formula as formula. I would like it to input the formula as a value.

I know I'm missing something but I dont know what.

Any Ideas?

lucas
07-05-2006, 09:56 AM
Maybe something like this will work...untested

ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=1,Range_L)))),AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=2,Range_L)))))"

ActiveCell.Value = ActiveCell.Value

Shazam
07-05-2006, 10:35 AM
Maybe something like this will work...untested

ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=1,Range_L)))),AVERAGE(IF(ISNUMBER(Range_L)" _
& ",IF(Range_L<>"""",IF(Range_C=2,Range_L)))))"

ActiveCell.Value = ActiveCell.Value




Hi Lucas thank you for replying. I tried your code and it went into a loop of some sort so I had to hit the esc button. Any Ideas?

OBP
07-05-2006, 10:43 AM
Did it do the loop because Lucas's code says
Activecell.value
but you appear to be working in
ActiveCell.Offset(-1, 3)

Cyberdude
07-05-2006, 10:51 AM
Just a thought ... can we assume that you do NOT have Application.Calculation = xlManual in effect? I write formulas into cells all the time, then remove the formula leaving the value. It would seem that what you are doing is correct, but xlManual would prevent it from working.

Shazam
07-05-2006, 10:53 AM
Did it do the loop because Lucas's code says
Activecell.value
but you appear to be working in
ActiveCell.Offset(-1, 3)


Please forgive me But I dont follow what you are saying. I'm just a
beginner in vba. How would I correct this problem?

OBP
07-05-2006, 10:56 AM
Shazam, my VBA is not very good, but I just used a very simplified version of your Formula array and it put the formula in the formula and the value in the Active cell.
Does it work if you just try the first part of the Formula?

The last part of Lucas's code says activecell.value = activecell.value, I thought perhaps it should say
Activecell.offset(-1,3).value = Activecell.offset(-1,3).value
but I did not put that line in my code at all at it worked as it should.

Shazam
07-05-2006, 11:13 AM
Shazam, my VBA is not very good, but I just used a very simplified version of your Formula array and it put the formula in the formula and the value in the Active cell.
Does it work if you just try the first part of the Formula?

The last part of Lucas's code says activecell.value = activecell.value, I thought perhaps it should say
Activecell.offset(-1,3).value = Activecell.offset(-1,3).value
but I did not put that line in my code at all at it worked as it should.



Thank You OBP that was it.




Activecell.offset(-1,3).value = Activecell.offset(-1,3).value

mdmackillop
07-06-2006, 09:19 AM
Rather than ActiveCell, you should use Target as your range

ie


For Each c In Cols
Target.Offset(0, c).FillDown
ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE

should be


For Each c In Cols
Target.Offset(0, c).FillDown
Target.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE


Assuming you are set to "Move After Enter", try running

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
MsgBox ActiveCell.Address
End Sub

Shazam
07-06-2006, 03:26 PM
Rather than ActiveCell, you should use Target as your range

ie


For Each c In Cols
Target.Offset(0, c).FillDown
ActiveCell.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE

should be


For Each c In Cols
Target.Offset(0, c).FillDown
Target.Offset(-1, 3).FormulaArray = "=IF(RC[-2]=1,AVERAGE




Hi mdmackillop,



Thank you for the input. Yesterday when I was testing the code it wasn't working as it suppose too. So I changed Active cell to Target and now it works perfectly.

Thank You for the response.