Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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.

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

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.

Related Topics:

- Formula using range name in VBA code
- Client side message (dialog) box
- i want a dialog box to pop out when user click the mouse
- How to display PRINT DIALOG BOX (?)
- custom dialogue box variable entry trouble
- excel vba word dialog box problem
- Dialog Box question
- calculating 'names' of text boxes in a form
- Black box - White box testing
- Fill List Box Based On Selection On Other List Box
- combo box/list box/checkbox Tool Tip
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Populate Formula(Function) in rows
- searching within formulas
- Three Leveled Formula
- R1C1 Formula
- Paste Formula help
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- combining formula
- How to add several conditions for formula "SUMIF"
- formula help but not vba please
- Loop in formula