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
- How to display PRINT DIALOG BOX (?)
- Client side message (dialog) box
- i want a dialog box to pop out when user click the mouse
- custom dialogue box variable entry trouble
- excel vba word dialog box problem
- Dialog Box question
- calculating 'names' of text boxes in a form
- combo box/list box/checkbox Tool Tip
- Fill List Box Based On Selection On Other List Box
- Black box - White box testing
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Stuck on copying formula from 1 sheet to another
- 2003 - Moving to a formula address
- Can Any one help me writing logic for the given formula
- Copy Value produced by formula in cell C3 to cell D3
- Quotations in Formulas and strings
- cells containing formula that refer to user-defined VBA function
- sum formula
- Manually creating a row outline with no formula anywhere
- Blank Formula bar