UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Double Click In Order To Update, Office 2013    
 
   
kenwood1
post Sep 6 2017, 09:42 AM
Post#1



Posts: 480
Joined: 11-July 09



Hi everyone,

I have put a formular through VBA on a cell, but it only give me #Name? and not the calculation. Only if I clicked on that cell with the mouse two time, this works. I have the total calculation. I would like this do automatically without that I presse the command button for an update.

Below is the code.

I appreciate for any help.

Thanks in advance.



Private Sub CommandButton2_Click()

' Put a formular on the cell
Cells(10, 5).Value2 = "=ANZAHL(A2:A65000)"


' Double click

Dim rRng As Range
Dim rCell As Range
Set rRng = Range("E4:E9")
For Each rCell In rRng.Cells
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
Next rCell


End Sub
Go to the top of the page
 
larai
post Sep 6 2017, 11:29 AM
Post#2



Posts: 1,016
Joined: 8-February 02
From: California, USA


When I add a formula to a cell using VBA I use .formula (absolute reference) or .formulaR1C1 (relative reference). I've never used .Value2.
Go to the top of the page
 
kenwood1
post Sep 6 2017, 12:41 PM
Post#3



Posts: 480
Joined: 11-July 09



Hi larai,

Thank you for your respond. Do you mind to write down how you would do. I have some issue when I write the formula "Cells(10,5).....? " unforutunatly did not show me a list that a can see or choose. So I do not get what you mean.

Thank for your time.

Go to the top of the page
 
kenwood1
post Sep 6 2017, 01:21 PM
Post#4



Posts: 480
Joined: 11-July 09



I got it thank you thumbup.gif


Range("G10").Formula = "=COUNT(A2:A65005)"
Go to the top of the page
 
larai
post Sep 6 2017, 01:57 PM
Post#5



Posts: 1,016
Joined: 8-February 02
From: California, USA


Great, glad you got it working!
Go to the top of the page
 
kenwood1
post Sep 7 2017, 02:27 AM
Post#6



Posts: 480
Joined: 11-July 09



hi larai,

How would you do, if you have a comma in the formula, like I have here with the "Y". It doesn't work. Any suggestion?


Range("I6").Formula = "=SUMMENPRODUKT(($B$3:$B$65000="Y")*($C$3:$C$65000=121))" iconfused.gif



Thanks again.
Go to the top of the page
 
kenwood1
post Sep 7 2017, 03:45 AM
Post#7



Posts: 480
Joined: 11-July 09



Hi larai

I have tried to put a double quotation. It shows on the the right formula, but I have again #Name?


Range("I6").Formula = "=SUMMENPRODUKT(($B$3:$B$65000=""Y"")*($C$3:$C$65000=121))"
Go to the top of the page
 
kenwood1
post Sep 7 2017, 03:57 AM
Post#8



Posts: 480
Joined: 11-July 09



Sorry to always bother you. I have use the methode below application.sendkeys and it works like a sharme. So thank you so so much.


Range("K6").Formula = "=COUNT(A2:A65005)"
Range("G6").Formula = "=SUMMENPRODUKT(($B$3:$B$9496=""N"")*($C$3:$C$9496=121))"


'Select Cell
Range("G6").Select

Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
Go to the top of the page
 
larai
post Sep 11 2017, 11:22 AM
Post#9



Posts: 1,016
Joined: 8-February 02
From: California, USA


Hi Kenwood1,

Sorry for the late response. I find when I need to add quotes to a formula, I use
& chr(34) & "Y" & chr(34) & ....

"=SUMMENPRODUKT(($B$3:$B$9496=""N"")*($C$3:$C$9496=121))"

would be

"=SUMMENPRODUKT(($B$3:$B$9496=" & chr(34) & "N" & chr(34) & ")*($C$3:$C$9496=121))"

Not sure why you're needing to use sendkeys. Should work without it unless you have a text formatted cell?
This post has been edited by larai: Sep 11 2017, 11:24 AM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2017 - 11:36 AM