Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Code not updating cell value

  Asked By: Tye    Date: Mar 02    Category: MS Office    Views: 1143
  

I have a line of code that puts a formula into a cell.

The code is placed in the cell but a zero value shows up until I go
into edit mode, then hit the enter button with no changes made.

Do I need somehow activate the cell after I enter the code?



the line of code is:
Worksheets(shCurrent).Cells(i + 13, 5) = _
"=IF(ISERROR(SUMIF('Detail INC " & SchM & "'!G:G,C" & i + 13
& ",'Detail INC " & SchM & "'!J:J))=TRUE,0,SUMIF('Detail INC " &
SchM & "'!G:G,C" & i + 13 & ",'Detail INC " & SchM & "'!J:J))"

to create in the cell:
=IF(ISERROR(SUMIF('Detail INC M00C'!G:G,C20,'Detail INC M00C'!J:J))
=TRUE,0,SUMIF('Detail INC M00C'!G:G,C20,'Detail INC M00C'!J:J))

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Seth Anderson     Answered On: Mar 02

In the rest of your code, do you use things like "application.screenupdating =
false"
??
this sometimes causes delayed problems.

 
Answer #2    Answered By: Jeanne Lawson     Answered On: Mar 02

yes, I use "application.screenupdating = false" but always end with
"application.screenupdating = true".

Are you saying I need to fash the screen to update the sheet?
ie
"application.screenupdating = false"
"application.screenupdating = true"

 
Answer #3    Answered By: Reamonn Fischer     Answered On: Mar 02

I've been playing around with numbler (https://numbler.com), an online
Excel-like spreadsheet program.
There are several programs that work with numbler, including Python, Ruby on
Rails, and C#. Does anyone know if they are planning on allowing VBA?


Please visit our website at
http://www.geocities.com/evan_j_siegel

 
Answer #4    Answered By: Aaron Evans     Answered On: Mar 02

Where appropriate, place

Cells.(x,x).Calculate

I have a column that I add a countif formula  which
resulted in zeros.

But I also had
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
in the sub that called this sub.

So following the formula insertion I added the
.Calculate & all appears to be good.

 
Answer #5    Answered By: Salvatore Kelly     Answered On: Mar 02

Thanks for the comments; however, I tried these tricks and it still doesn't
work.

The only thing that seems to work is to hit  F2 then Enter.
I tried SendKey "F2" and SendKeys "Enter" but it didn't help.

Any other thoughts???

 
Answer #6    Answered By: Deloris Harris     Answered On: Mar 02

I ended up re-runing the same code  after more data was added was added to the
other cells and it seems to work now. I guess excel wants the data in place
before the formula.

The logic I changed was:
Worksheets(shCurrent).Cells(i + 13, 5) = _
"=IF(ISERROR(SUMIF('Detail INC " & SchM & "'!G:G,C"
& i + 13
& ",'Detail INC " & SchM &
"'!J:J))=TRUE,0,SUMIF('Detail INC " &
SchM & "'!G:G,C" & i + 13 & ",'Detail INC " & SchM &
"'!J:J))"

changed to:
Worksheets(shCurrent).Cells(i + 13, 5).Formula = _
"=IF(ISERROR(SUMIF('Detail INC " & SchM & "'!G:G,C"
& i + 13
& ",'Detail INC " & SchM &
"'!J:J))=TRUE,0,SUMIF('Detail INC " &
SchM & "'!G:G,C" & i + 13 & ",'Detail INC " & SchM &
"'!J:J))"

Since the default to Cells(R,C) is value, I was confusing Excel by loading a
formula as a value (so I read on the web). Didn't help the process, I still have
to re-run the code, but it looks better.

I was thinking, Excel didn't load the values due to link to other sheets but
refreashing the links didn't help either. I wish I understood why this works,
but it does, and the client is happy.

 
Answer #7    Answered By: Luisa Fischer     Answered On: Mar 02

When you say "... due to link to other sheets ...", I think you actually
have links to another workbook (i.e. different file). Excel has never been
terrifically good at grabbing information out of other workbooks.

I suspect that your problem is with the use of formulas that refer to
external workbooks. Worse, you're actually using functions (SumIf) to
access the external book, and they don't necessarily work well with external
references at the best of times.

What's your purpose in this? There might be a more reliable method. E.g.
simply open the external file in VBA and copy across what you need; or use
SQL to access the external workbook.


AFAIK it makes no difference whether you assign a value to the .Formula or
the .Value attribute of a cell. The difference comes when you are taking a
value out of a cell.

 
Answer #8    Answered By: Eshe Chalthoum     Answered On: Mar 02

All references in the workbook are to the same workbook, just different sheets.
When I look at Edit/Links there are several but they are all inside the
workbook. This is why I thought maybe the links weren't being updated.

I got the information about .value vs .formula from
http://www.ozgrid.com/forum/showthread.php?t=86436
The default property for a RANGES
\r\nNamed Ranges. SheetLevel Named Range, Named Constants, Named Formulas
,Relative Named Ranges ,Dynamic Named Ranges ,Advanced Dynamic Named Ranges .
\r\nWorking With Ranges. FindLast cell/row/column etc.
\r\nFormula Reference Changer. Absolute to Relative etc.
\r\nSpecialCells Method
\r\nExcel Named Range Manager $
\r\n
---------------------------------
OFFSET RANGES VBA
\r\nReturns a Range object that represents a range that’s offset from the
specified range. Read-only.
\r\nexpression.Offset(RowOffset, ColumnOffset)
\r\n\"expression\" is required and should be a Range object.
\r\n
\r\nRowOffset: Optional Variant. The number of rows (positive, negative, or 0
(zero)) by which the range is to be offset. Positive values are offset downward,
and negative values are offset upward. The default value is 0.
\r\n
\r\nColumnOffset: Optional Variant. The number of columns (positive, negative,
or 0 (zero)) by which the range is to be offset. Positive values are offset to
the right, and negative values are offset to the left. The default value is 0.
\r\n
\r\nExample
\r\nSub OffsetMe()
\r\n MsgBox Range(\"B2\").Offset(RowOffset:=-1, ColumnOffset:=2).Address
\r\nEnd Sub
\r\nIs the SAME as;
\r\nSub OffsetMe()
\r\n MsgBox Range(\"B2\").Offset(-1,2).Address
\r\nEnd Sub
\r\n
---------------------------------
OFFSET FORMULA
\r\nReturns a reference to a range that is a specified number of rows and
columns from a cell  or range of cells. The reference that is returned can be a
single cell or a range of cells. You can specify the number of rows and the
number of columns to be returned.Syntax =
OFFSET(reference,rows,cols,height,width)
\r\n
\r\nExamples
\r\n=OFFSET(C3,2,3,1,1) Displays the value in cell F5.
\r\n=OFFSET(C3:E5,0,-3,3,3) Returns an error, because the reference is not
valid.", "style=\"background: #FFFFFF;padding: 2px;font-size: 10px;width:
550px;\"");' style="FONT-STYLE: italic" onmouseout=GAL_hidepopup();
href="www.ozgrid.com/.../autolink.php"\
>range is .Value
This line  is entering the FORMULAS/FUNCTIONS
\r\nTIP: To show all formulas use Ctrl+;
\r\nMicrosoft Excel Formulas, Tip, Tricks and Feature Examples
\r\nStop formula  Viewing
\r\nExamples of Commonly Used Formulas
\r\nList & Examples of All Formulas
\r\nExcel Formula Manager $
\r\n
\r\nNESTED IF LIMITATION
\r\nNested IF Limitation Work-around. Preferred Method
\r\nNested IF Limitation Work-around 2
\r\n
\r\nCUSTOM FORMULAS/FUNCTIONS UDFs
\r\nCustom Functions", "style=\"background: #FFFFFF;padding: 2px;font-size:
10px;width: 550px;\"");' style="FONT-STYLE: italic" onmouseout=GAL_hidepopup();
href="www.ozgrid.com/.../autolink.php\
">formula as a cell's value rather than a formula, which gives uncertain
results.

 
Answer #9    Answered By: Waggoner Fischer     Answered On: Mar 02

To expand on your comment re: grabbing info from other
workbooks, we routinely grab info from other
workbooks, BUT we now only use raw data versus
calculated data, especially when it comes to tracking
dates & time. One workbook tracks ~50 program
management workbooks & not all are updated at the same
time so we capture their inputs then calculate in the
tracking workbook.

 
Answer #10    Answered By: Davi Costa     Answered On: Mar 02

Yes, they're clearly cross-sheet links, not
cross-workbook links.

That particular reference http://www.ozgrid.com/forum/showthread.php?t=86436
is a little obscure. The person asking the question is trying to put R1C1
formulas into cells without using .FormulaR1C1. I doubt that Excel would
ever be able to cope successfully with that. It needs to be told that the
formula it's being given is R1C1 format, or else it just won't know.

You're using column-letter/row-number format, which won't present the same
problem.

However, you SHOULD be using .Formula anyway - to remind yourself that
you're inserting a formula, if nothing else. If it helps it work better, so
much the better.

 
Didn't find what you were looking for? Find more on Code not updating cell value Or get search suggestion and latest updates.




Tagged: