Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










CountA Function Using VBA


How can I make it work ?

Sub test2()
Dim Wbk, aaa, bbb
Wbk = "\server1folder1file1.xls"
aaa = Application.WorksheetFunction.CountA(Workbooks(Wbk).Sheets(1).Range("A:A"))
bbb = MsgBox(aaa)
End Sub


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
COUNTA Function After Blank Cell
Is there a way to use the COUNTA function in cell A1, that will return the total number of cells containing text in the rest of row 1, BUT, will start the count over after a blank cell.

EXAMPLE: A2, A3, A4 all have text added on a daily basis. The value returned via COUNTA in A1 = 3 (after the third day). However, A5 is blank, then A6 gets text on the 5th day, and now I need the returned value in A1 via the COUNTA function to = 1, because we started over after a blank cell.

Possible?

View Replies!   View Related
VBA CountA Function For Variable Range
I am trying to produce VBA code that will perform the CountA function on a variable number of lines in a column. My macro first sorts the worksheet by department number and then inserts 2 blank rows when there is a change in departments. Most departments have several rows of data, but some may have only 1. In column N of the first blank row following each department, I want to count the number of rows for that department.

View Replies!   View Related
Countif Function In A Macro: SUM And COUNTA Works
I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.

Sub B_Test()
Dim myRange
Dim Results
Dim Run As Long
myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))
Range("M3") = Application.WorksheetFunction.Sum(myRange)
myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))
Range("D3") = Application.WorksheetFunction.CountA(myRange)
End Sub

I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.

View Replies!   View Related
Counta And Validation
How can I add validation to cell B76?

If there is no text in B71:B75 then don't allow a value in B76.

This would force them to add text description before entering a price.

View Replies!   View Related
Counta With A Tweak - Countif(?*)
How do I count the number of cells in a range that has text, even though all cells has a link....

View Replies!   View Related
Multiple IF & COUNTA Functions
I've tried many variations but I can't seem to find a solution - I hope you can help. I'd like a formula that will ideally achieve the following:

IF B5:F5 all contain the letter y then return "Pass"
IF B5:F5 AND G5:K5 all contain the letter y then return "Merit"
IF B5:F5 AND G5:K5 AND L5:K5 all contain the letter y then return "Distinction"
IF B5:F5 is blank return blank
IF there are Y's in B5:F5 and the total < 5 return "Fail"

I did a sample sheet to show the desired output but don't know if this forum supports attachments.

View Replies!   View Related
CountA And Blank Cells
I understand the non blank cells part of Count A but not the additional values part. I have looked at the examples in teh MS help and it doe not help at all really.

A1:Data
A2:Sales
A3:11/01/1900
A4:
A5:19
A6:3
A7:FALSE

doing =counta(a1:a7,2) returns a value of 7 but why? There are six non blank cells and no cells containing a 2

View Replies!   View Related
Dynamic CountA Values?
Say there are two changing values in a spreadsheet: A row reference, and a column reference, called sRow and sCol.

sRow and sCol are integers, and they are also changing values.

I want to know how to find how many cells have something in them on row R, starting from column C to infinity (To the right). But the problem is that R and C change sometimes, so I have to make some sort of dynamic CountA formula.

I know I have to use CountA() to do this, but I don't know how to make the range reference just from two integers. What I have tried so far is CountA($sCol$sRow:$sRow), but this does not work. How do I properly format this formula?

View Replies!   View Related
Counta Within A Range Of Numbers
A cell that will count an entry in another cell, within a range of numbers. EX: cell g1 will count the entries that range from 1000 to 1999 and 5000 to 5999 and 7000 to 7999. cell h1 will count the entries that range from 2000 to 2999 and 6000 to 6999 and 8000 to 8999. So if i had 6 cells with the following entries:

cell a1 - 1010
cell b1 - 2230
cell c1 - 5699
cell d1 - 6521
cell e1 - 7001
cell f1 - 8035

cell g1 would show 3
cell h1 would show 3

View Replies!   View Related
Combining 'counta' And 'countif'
I'm trying to create a report to show daily statistics for internet traffic. I have a large table showing a daily breakdown of performance, a table showing combined cost for all "Mondays", "Tuesdays" etc in the month and I also want a table to show the "average" performance for a typical "Monday", "Tuesday" etc.

The report will be used on a daily basis and I realised that until there is a full compliment of data the daily averages table would be inaccurate as it will be dividing the overall total for each day by the total number of occurances each day has in a typical month. This is wrong because if we are only halfway through the month then we will be dividing by days that haven't arrived yet.

View Replies!   View Related
CountA - Dynamic Range - Macro
I recorded this macro based on a column with a drop down window in which data has been filtered .....

View Replies!   View Related
Setting Named Ranges (OFFSET/COUNTA)
I am a novice excel and VBA user, but have been working the past few days on a project to create a data entry form (using Excel 2003 on WinXP). I have the userform created and the textboxes working fine for inputting data into the spreadsheet. I also have comboboxes set up, and if I directly input data into them, they also input that data. So far, so good.

(problem)

Here is my issue: I want the comboboxes to use dynamic / variable ranges, so I have been trying to set up the ranges by defining them as names. I've come across a bunch of examples, but none of them seem to work (all produce an error that the formula is not correct or instead seem to reset the name to just the first cell when I input it).

My sheet is called Dropdown Lists, and there is a column here for each combobox. Each row in a column has the options I'd like used for that combobox. So I am basically trying to name a range as the entries in just one column.

This is what I have been working with most recently in the Name - > Define dialog box that according to everything I read -SHOULD- be working:

=OFFSET('Dropdown Lists'!$A$3,0,0,COUNTA('Dropdown Lists'!$A:$A),1)

This produces an error, that when you hit OK highlights "$A$3" - and if I remove the $ symbols, it skips then to the "$A:$A" until I remove THOSE $ symbols, and then excel will accept it, but when I go to the name, it only highlights one cell. I thought maybe that was OK, but when I tried putting the name into my rowsource, nothing happened - so apparently not.

Questions:

(main question)
- Why will this not input correctly when everything I read suggests this format? Am I not handling the space in the worksheet name correctly (with ' ')? Sometimes excel will automatically add in " " between = and OFFSET and the closing at the end, but this also has not helped. One source I read even recommended taking off the = at the start (also did not help). Am I missing something here?

(follow-up questions)
- Why are you supposed to use "$" before the cell references anyway? I've looked for an answer but haven't found one yet... is that for when you make cell references to cells on an external worksheet?

- What is the "Add" button for on the Name -> Define dialog box? I'm confused to if I am supposed to use this to add the name to the list of defined names, or just hit OK?

- At the bottom of the Name -> Define dialog box is the "Refers To:" field with the formula. On the right of that you can hit a button to go to what seems like another (smaller) formula input box. Why would I ever use that when I could just type it in on the previous box? There is no label for it so I am guessing here - but does it actually do something else (like you need to go to that smaller dialog to input new formulas and the previous box is only for -viewing- formulas, or?)?

View Replies!   View Related
COUNTA And Sum: Counts How Many Week Days There Are In A Particular Month
I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)
Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.

View Replies!   View Related
Using COUNTA To Retrieve The Number Of Cells That Are Not Blank.
I am trying to use =COUNTA(A25:A500) to retrieve the number of non blank cells. Only problem I am having is that the cells that are blank are also using a formula to gather its data. The cell would be blank but the formula is still there, so using =COUNTA(A25:A500) returns all these cells with the formula also.

View Replies!   View Related
Setting Counta Range Based On A Date
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:

View Replies!   View Related
Sumproduct OR Adding Criteria To Countif, Counta And Average
I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”

CURRENT FORMULA

I: “=COUNTIF(C2:C3100,"

View Replies!   View Related
"counta" Count Empty Cells
I am working with output that I copied from SPSS into Excel. I want to use "counta" to count the number of cells in particular rows or columns that have data in them.

I have used "counta" successfully in the past. However, this time it is counting the blank cells as well as those that contain data. When I click on one of the "blank" cells in Excel, nothing shows up in the formula bar.

Why is Excel counting the empty cells?

View Replies!   View Related
COUNTA Not To Counting Space Bar Space
I have the following formula that works fine until someone uses the space bar to clear a cells contents

=COUNTA($D11:$AI11)

When the space bar is used to clear a cells contents the COUNTA statements includes the space in the count. How do I count the number of cells with content and exclude the space bar space in a cell?

View Replies!   View Related
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View Replies!   View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

View Replies!   View Related
Nested IF Function Error (entered Too Many Arguments For This Function)
I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

View Replies!   View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

View Replies!   View Related
User Defined Function Function Tooltip
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?


View Replies!   View Related
2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2.

Cell E17 = 77/170
Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....

View Replies!   View Related
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

View Replies!   View Related
Show Insert Function Dialog Pre Set To Function
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

View Replies!   View Related
AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

View Replies!   View Related
Vlookup Function Together With An Offset Function
I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

View Replies!   View Related
Write The VBA Function Through The Function Wizard
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

View Replies!   View Related
Work Around To The 7 Function Limit With The IF Function
I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).

I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.

View Replies!   View Related
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

View Replies!   View Related
Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

View Replies!   View Related
Function Back Variables: Function Give Two Or More Output Variables
Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View Replies!   View Related
Use The OR Function Inside An IF Function
I am trying to use the OR function within an IF condition.
when I use each function seperatly, it works fine. but both functions
together always return the value of "B" - as false result, even when the
requested cell holds one of the true values.

for example:
=IF(B18=OR(148,150),"A","B")

View Replies!   View Related
Changing A VBA Function Into An IF Function
i am using the following code to create a function in excel

i have to put the worksheets onto mac's so i cannot use a macro

can anyone help me change this into an iF function

load is cell D9 and the factor is in cell E9

Function Pmax(Load, Factor) As Double
Application.Volatile
Select Case (Factor)
Case "0"
Pmax = ""
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"

View Replies!   View Related
Rand() Function In Another Function
I want to call on the existing rand() function as a part of the function i've written, however I'm getting an error saying sub/function not defined when I go to compile. How do I have to define rand() so that vba knows that I want the use the existing function?

View Replies!   View Related
Sum Function With Address Function
On my worksheet =ADDRESS(18+BH8-1,60,4) gives me BH21. However = SUM(BH18&":"&ADDRESS(18+BH8-1,60,4)) gives me the #VALUE! error

View Replies!   View Related
Replace Function "Sub Or Function Not Defined"
I'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.

View Replies!   View Related
VBA Round Function Vs Worksheet Round Function
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??


View Replies!   View Related
VBA GPA Function
Currently I am using this formula =AVERAGE(CHOOSE(MATCH(A1:E1,{"A+","A","A","B+","B","B-","C+","C","C-","D+","D","F"},0),4,3.8,3.6,3.4,3.2,3,2.8,2.6,2.4,2.2,2,0)) to calculate the GPA's and it works fine. What I am trying to do is to wite a function for GPA's and I am coming up short. I am still trying to learn VBA can someone please guide me. Thanks in advance Stephen.

Function gpa(r As Range) As String

On Error GoTo Fail

For Each cell In r
Select Case UCase(Trim(r))

Case "A+": gpa = 4#
Case "A": gpa = 3.8
Case "A-": gpa = 3.6
Case "B+": gpa = 3.4
Case "B": gpa = 3.2
Case "B-": gpa = 3#
Case "C+": gpa = 2.8
Case "C": gpa = 2.6
Case "C-": gpa = 2.4
Case "D+": gpa = 2.2..........


View Replies!   View Related
Getting The Row Function
If I'm using the small function. How do I get the row instead of the actual value?


View Replies!   View Related
What Does The Dir Function Do?
What does the Dir function do? I've used the object browser and help but I still dont get what it does!

View Replies!   View Related
Between Function
Between function in excel ...

View Replies!   View Related
MID Function
I have a column wiht a series of characters like this:

ABCDEF+966789

The column is repeated like this all the way down but with different numbers.

How do I correctly use a formula like this:

=MID(A1,5,255)

I only want the characters on the right side to show up in the row.

View Replies!   View Related
Function In VBA
why the below code does not work?

Sub AnsiSub()
ActiveSheet.Cells.Select
Selection.Formula = Value(Trim(Substitute(A3, char(160), char(32))))
MsgBox ("Complete")
End Sub

View Replies!   View Related
IF/AND Function
I have data relating to staff members and application outcomes. I need to total the instances of each outcome for each staff member in another table. So for example if one column reads "John Smith" and the next column reads "offer made" i need to total the number of instances that that appears in the data.

View Replies!   View Related
IF And SUM Function
I want to do is take a number from column A (the player level) and do a sum function in column B from the current level to 19 (columns B,C,D,E represent seperate guilds and exp required for that level/guild).

So it will look like this (underline=input, bold=return output):

Current level: 12 EXP to 19: (b12:b19)

11,0001,0001,5005,000
21,6003,2004,80016,000
33,4006,80010,20034,000
46,00012,00018,00060,000
59,50019,00028,50095,000
614,00028,00042,000140,000
719,60039,20058,800196,000
826,40052,80079,200264,000
934,50069,000103,500345,000
1044,00088,000132,000440,000
1155,000110,000165,000550,000
1267,600135,200202,800676,000
1381,900163,800245,700819,000
1498,000196,000294,000980,000
15116,000232,000348,0001,160,000
16136,000272,000408,0001,360,000
17158,100316,200474,3001,581,000
18182,400364,800547,2001,824,000
19209,000418,000627,0002,090,000
Totals
1,264,0002,527,000 3,790,500 12,635,000

View Replies!   View Related
Using Len() Function
I'm having a problem using Len() function. I am trying to count the number of characters in an range using an array function.

{=SUM(LEN(CTFUpload))}

The problem arises with dates. I have the dates in the format dd/mm/yyyy which is ten characters however the len() function counts it as 5 characters i.e. it counts its numeric value.

The character count is based on how it will appear on a txt file.

View Replies!   View Related
Min Or Max Function?
i dunno what function i should use, in this kind of situation.

if there is value in Cell A5 , Cell A7 should return the value in A5.

Cell A1 12pm
Cell A2 6pm
Cell A3 9pm
Cell A4 12am
Cell A5 8am

Cell A7 8am



however, if there is no value in Cell A5, Cell A7 should return the value in A4(the previous value before A5).

Cell A1 12pm
Cell A2 6pm
Cell A3 9pm
Cell A4 12am
Cell A5



View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved