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

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"

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

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.

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

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.

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.

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.

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.

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.