Excel VBA Problem

I am trying to get an automatic 120 and 90 day average based on today's date. I can get the row/column numbers, but when I "create" the function in VBA I get text in the cell. If I manually click to edit the cell, and then hit enter, it "becomes" a function, and I get the correct answer. Why won't VBA let me put it in as a function/equation?

I have attached a "stripped" copy of the workbook. If you click on the dice, and then look at F51 and G51 you will see what I mean. I have several Excel and VBA programming books, but I can't seem to find this problem. The workbook is Excel 2003.

Ok, I lied. I don't know how to get you a copy to look at. The macro is in a Word 2003 document attached.

Thanks for the help.

 

Hi SeaDragon.
I have to tell you, it took me 4 1/2 hours to figure this out :confused, so I sure hope it works!!!
It looks like you had some stuff in there to try to help things along, so I have taken it out of the following code.
The trick I used in the end was to put this line in after each formula:

Code:

'Force Excel to calculate formula
    Range("F51") = Range("F51").Value

So the code now looks like this See the note below the code, as well):

Code:

Sub AVG120()
'
' AVG120 Macro
' Macro recorded 25-06-2011 by James
'
' Keyboard Shortcut: Ctrl+k
'
'   Create 120 and 90 Day Averages
'
    Range("F51").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""=Round(Average($B"",R[-3]C[2],"":$B"",R[-3]C,""),2)"")"
    
'Force Excel to calculate formula
    Range("F51") = Range("F51").Value

'
'   Create 90 Day Average
'
    Range("G51").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""=Round(AVERAGE($B"",R[-3]C,"":$B"",R[-3]C[-1],""),2)"")"

'Force Excel to calculate formula
    Range("G51") = Range("G51").Value
    
    Range("A1").Select
    
End Sub

O, I also took out the $ signs after each "$B" in the formula. It didn't seem to like them, and I couldn't get the formula to work with them in.

P.S. You can post CODE by pasting it in the box, selecting it, and clicking on the hash in the toolbar at the top of the box.

 

GCWesq:

THANK YOU! It works beautifully, I can even keep the $ in the code.

I Really appreciate all your work.
:-D

 

You're welcome. Very glad it works.
Strange about the $ - I couldn't get it to work with that in, even though I'm also using Office 2003. :confused

 

Oops! Logged in on my son's automatic logon earlier. :-o
Figured out the $ thing. I was only using a bit of test data in my Word doc, and didn't have any values in the critical reference cells... just in case you were going to lose sleep over what was happening there. :-D

 

GCWesq:

I only had one problem, it left the "final cell" active so I added one line, here is the final code:

Sub AVG120()
'
' AVG120 Macro
' Macro recorded 25-06-2011 by James
'
' Keyboard Shortcut: Ctrl+k
'
' Create 120 and 90 Day Averages
'
Range("F51").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""=Round(Average($B$"",R[-3]C[2],"":$B$"",R[-3]C,""),2)"")"
Range("F51").Select
'Force Excel to calculate formula
Range("F51") = Range("F51").Value
'
' Create 90 Day Average
'
Range("G51").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""=Round(AVERAGE($B$"",R[-3]C,"":$B$"",R[-3]C[-1],""),2)"")"
Selection.Copy
'Force Excel to calculate formula
Range("G51") = Range("G51").Value
' Jump out of Cell
Range("G51").Select
Application.CutCopyMode = False '<=== to close G51
' Reposition Worksheet for use
Range("A1").Select

End Sub

This now works great. Thanks again for all your help.

 

Any time. A very satisfying result.