Entering formulas from VBA doesn't work.

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

Select allOpen in new window

BFN,

fp.

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...

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?



Re: The previous question thread

Did you mean the comment (linked) below?

[ https://www.experts-exchange.com/questions/28259727/Check-if-substring-is-found-in-range-of-strings.html?anchorAnswerId=39554722#a39554722 ]

No. I meant this: https://www.experts-exchange.com/questions/28259727/Check-if-substring-is-found-in-range-of-strings.html?anchorAnswerId=39557900#a39557900

IVer

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:
Then, after I click in the formula field:and when I hit the Enter-key:

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

IVer

In your actual code do you have

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

or

...formula = "=SUMMER(....

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

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

Also try

Ws.Cells(Rl, I).Formula = Ws.Cells(Rl, I).text

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.

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

Idiotic error all down to myself! Sorry for all who spent time on this trying to help me!

Brgds IVer in Oslo, Norway