Entering formulas from VBA doesn't work.

Hi!

If I enter this in VBA:
Ws.Cells(Rl, I).Formula = "=SUM(" & Chr(64 + I) & _
    "10:" & Chr(64 + I) & (Rl - 1) & ")"

Open in new window

I get the following in the Excel Cell (if Rl is 140 and I is 11):

=SUM(K10:K140)

which seems just fine to me. However I get a #Name?-error displayed rather than the sum. Why is this?

If I then go to the cell in the Excel sheet and just click in the formula field and press the Enter-key, I get the correct value in the cell.

Please help!

Brgds
Iver in Oslo
Iver Erling ArvaSenior consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

That is strange!  Your code worked as expected for me.

Please try this change:

Public Sub Q_28262128()
  
  Dim I                                                 As Integer
  Dim Ws                                                As Worksheet
  Dim Rl                                                As Long
  
  Set Ws = ActiveSheet
  
  Rl = 140
  I = 11
  
' Ws.Cells(Rl, I).Formula = "=SUM(" & Chr(64 + I) & "10:" & Chr(64 + I) & (Rl - 1) & ")" ' *** CHANGED; see below

  Ws.Cells(Rl, I).Formula = "=SUM(" & Range(Cells(10, I), Cells(Rl - 1, I)).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
  
  Set Ws = Nothing
  
End Sub

Open in new window


BFN,

fp.
0
Iver Erling ArvaSenior consultantAuthor Commented:
Hi again, fanpages!

I get exactly the same. E.g. =SUM(K10:K140) in the formula field and #NAME? as result until I click in the formula field and press Enter. Then the K10:K140 turns blue and I get a sum in the cell.

Btw: did you see my last comment to you in Q_28259727?

IVer

PS! I have Office 2013...
0
[ fanpages ]IT Services ConsultantCommented:
I only have use of MS-Office/Excel 2010 at present (but will be able to use MS-Excel 2013 later today).

Just to confirm, these are the Excel Options for Formulas I am using.  Are your options similar/the same?

MS-Excel 2010 - Excel Options - Formulas

Re: The previous question thread

Did you mean the comment (linked) below?

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28259727.html#a39554722 ]
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Iver Erling ArvaSenior consultantAuthor Commented:
0
Iver Erling ArvaSenior consultantAuthor Commented:
Regarding Excel Options. My settings are exactly the same, and this window also looks almost 100% the same in Excel 2013.

Also see how it looks:Data entered in cell from VBA
Then, after I click in the formula field:I have clicked in formula fieldand when I hit the Enter-key:And the value is calculated...

I wonder why this is! Probably just some security setting somewhere. Or a bug?!

IVer
0
Saqib Husain, SyedEngineerCommented:
In your actual code do you have

...formula = "=SUM(....

or

...formula = "=SUMMER(....
0
Saqib Husain, SyedEngineerCommented:
Try something like


Ws.Cells(Rl, I).Formula = "=SUM(" & Chr(64 + I) & "10:" & Chr(64 + I) & (Rl - 1) & ")"
Ws.Cells(Rl, I).Formula = Ws.Cells(Rl, I).Formula
0
Iver Erling ArvaSenior consultantAuthor Commented:
Hi, ssaquib!

I have to use "=SUMMER(..."

Regarding your suggestion, I have tried it, but it doesn't change anything. Good thinking though ;-)


TO ALL:

To solve it for now I will just do the calculations in VBE and enter the calculated values in Excel.

IVer
0
Saqib Husain, SyedEngineerCommented:
Also try

Ws.Cells(Rl, I).Formula = Ws.Cells(Rl, I).text
0
Iver Erling ArvaSenior consultantAuthor Commented:
OH MY GOD!!! IS IT POSSIBLE???

I must have done something wrong when I first tried to enter the sum formulas into the cells from VBA. And when I enter them manually via Excel I have to enter "SUMMER=" so when I tried to enter SUM= and got an error message I assumed that the language was the error.  BUMMER!

The error was something else. So now, when I have spent all day trying to figure out ways around the problem I changed SUMMER back to SUM by accident, and it works.

SORRY for wasting your time!

IVer
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
So, when you said above that you were using =SUM you were actually using =SUMMER, & you didn't actually change the code to how any of us suggested, you continued to use =SUMMER?

No, it wasn't a waste of time.  It isn't summer until 21 June 2014.
0
Iver Erling ArvaSenior consultantAuthor Commented:
fanpages:

"So, when you said above that you were using =SUM you were actually using =SUMMER, & you didn't actually change the code to how any of us suggested, you continued to use =SUMMER?"

Yes, that is correct. I was so 110% sure I had to do it. I said it because I didn't want to confuse you. I can tell you it is very annoying to say the least to have to work with two different languages in Excel. Just finding the right commands is timeconsuming at best and it is so unnecessary. Why couldn't they stick with one programming language? I don't think there is a Norwegian version of C#, C++ or PL/SQL!

But, one good thing that came from this is that now I know how to find the Norwegian version of an Excel command at least. It's just entering the English version from VBA and see what shows up in Excel ;-)

All the best from
Iver in Oslo
0
Iver Erling ArvaSenior consultantAuthor Commented:
Idiotic error all down to myself! Sorry for all who spent time on this trying to help me!

Brgds IVer in Oslo, Norway
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.