Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Named formula/Windows dialog boxes

  Asked By: Mona    Date: Mar 02    Category: MS Office    Views: 4720
  

I've been programming in VBA for several years, but am fairly new to
this group. In reading the recent posts, I have to say thanks to all
of you for your time and efforts to help out us poor geeks. I have
recently encoutered two new problems and that's why I write.

Problem 1: I have created a named formula that calculates the number
of blanks cells in a dynamic range. The formula is:
=COUNTBLANK(INDIRECT("All!$Q$2:$Q$" & COUNTA(All!$A:$A)))
It works great on a sheet, but I can't seem to reference the
calculated result in code. The properties of the name object return
the formula itself, but not the result. I tried putting the formula
directly in code without success.

Problem 2: How does one prevent Windows from displaying "helpful"
dialog boxes (i.e. when a large amount of data is on the clipboard
and the file is closed, Windows asks if you want to retain the data
on the clipboard).

I've been pulling my hair out (what's left of it) and wondering if
I'm old and losing it (which is a possibility). Please help.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Tyrone Sanchez     Answered On: Mar 02

Have you tried the macro recorder to see what code is generated when you do
the process manually?

It is never great code, but it may give you an idea of what is needed.

 
Answer #2    Answered By: Jonathan Brown     Answered On: Mar 02

There may be more efficient methods for a solution....
see if this fits the bill:

SourceSheet = Sheets("All").Index
ColumnQ = Sheets(SourceSheet).Range("Q1").Columns.Column
NonBlankCount = WorksheetFunction.CountA(Sheets(SourceSheet).Range("A:A"))
CountBlankRange = Range(Cells(2, ColumnQ), Cells(NonBlankCount,
ColumnQ)).Address
BlankCount =
WorksheetFunction.CountBlank(Sheets(SourceSheet).Range(CountBlankRange))

 
Answer #3    Answered By: Husani Chalthoum     Answered On: Mar 02

I know I am responding to my own question, but I found two efficient
ways of solving Problem 1 (returning the number of blanks in a
dynamic range  in code) that I thought the members might be interested:

Solution 1: ActiveWorkbook.Names
("ranAllUploadCol").RefersToRange.SpecialCells(xlCellTypeBlanks).Count

Soltion 2: WorksheetFunction.CountBlank(ActiveWorkbook.Names
("ranAllUploadCol").RefersToRange)

In both of these solutions, ranAllUploadCol is a named  dynamic range.
Thanks for your input AJ. Your solution works great. I'm still
looking for the solution to Problem 2.

 
Didn't find what you were looking for? Find more on Named formula/Windows dialog boxes Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post