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:
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
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
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 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
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
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? (followup 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
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
"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
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
VBA Function To Sub
I have a userdefined type as follows: Type DocketIdent cStore As String cSuburb As String cPostcode As String cCheckOutID As Integer cDatePurchase As String cTimePurchase As String cInvoiceNo As String cNoOfItems As Integer End Type I have a function that uses a DocketIdent variable and assigns values as shown above to it. i.e. cStore, cSuburb etc. I'm trying to return these individual Docket details from a Function to a Sub procedure. Is this possible and would I have to return each field of the type individually?
View Replies!
View Related
Function Instead Of One Within Vba
I try to use a worksheet function instead of one within vba. Here's what I want to do: Return the value from a cell within a vertical range named "Phases Active." The value to be returned is the one in the same row as the non blank cell 1 row to the left of this range. So, I can use "x" to make a selection of the range basically. VLookup, right? =VLOOKUP((ISBLANK=FALSE),PhasesActive,FALSE)
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
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
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
#value Error In VBA Function
my formula called ' conversion' used to convert foreign money in EUR does not seem to work. My formula gives a value error. I wonder why... Function Conversion(Position As Integer, Currency As Integer) As Integer Dim USD As Integer Dim GBP As Integer Dim CHF As Integer Dim JPY As Integer Dim NOK As Integer Dim NZD As Integer Dim SEK As Integer Dim ZAR As Integer Dim ISK As Integer Dim TRY As Integer Dim AUD As Integer ' here i give a destination to my forex quotes who are in a excel sheet USD = Range("N9").Value GBP = Range("N10").Value CHF = Range("N11").Value JPY = Range("N12").Value NOK = Range("N13").Value NZD = Range("N14").Value SEK = Range("N15").Value ZAR = Range("N16").Value ISK = Range("N17").Value TRY = Range("N18").Value AUD = Range("N19").Value
View Replies!
View Related
VBA Function Not Working
I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set" I have made sure that Microsoft scripting is referenced. any clues would be appreciated. I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started. {code] Function BrowseForfolderShell() As String Dim objshell As Object, objfolder As Object Set obshell = CreateObject("Shell.Application") 'Uncomment next line to start at desktop 'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0) Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:") If (Not objfolder Is Nothing) Then
View Replies!
View Related
VBA IF Function Or If IsEmpty
I need a IF funtion to look at a series of cells and if the cells are empty I want it to proceed to userform1. If the cells have data already i want it to go to userform5. I tried IF IsEmpty(cell list)=True Then UserForm1 and IF IsEmpty(cell list)=False Then UserForm5 But it only bring up userform5 no matter what the cells have in them.
View Replies!
View Related
Yield Function In VBA
I just upgraded to Office 2007 and am having trouble getting the Yield function to work in a macro I wrote in office 2003. I have added the analysis tool pack and the analysis toolpack for VBA and it still does not work. The error I get is that the object or property is not supported. All variables used in the function are assuming the correct values. MarketYield = Application.Worksheetfuntion.Yield(Date1, BondMaturityDate, TotalWAC2, MarketPrice2, 100, 2)
View Replies!
View Related
VBA Left Function
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122. So far my programming only returns the 280000 into the cell. I am working with cell values in column "K".
View Replies!
View Related
Replace Function In VBA
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website: [url] Cells.Replace What:=Chr(63), Replacement:="" 'finds and replaces the  that converted to boxes I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.
View Replies!
View Related
MOD Function In VBA
In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.
View Replies!
View Related
Use The Iferror Function In VBA
I'm trying to use the iferror function in VBA and the whole code is not executing, as the syntax error is being noticed first, and not allowing the whole code to execute. What I want to happen is if the vlookup returns an error, the cell value should be "*". The code is:
View Replies!
View Related
Find Function In Vba
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code. Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If Find = True And ActiveCell.Next.Next.Value <> "" Then Find = Empty Else MsgBox "The currency " & local_currency & " does not exist in the currency list" Sheets("Remarks").Select End If
View Replies!
View Related
Maxif Function In Vba
I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell: = SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6))) Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types  without success. Can anybody help me? Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range) AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))" End Function
View Replies!
View Related
Countif Function In Vba
I'm trying to use the worksheet function countif in my code and it does not recognize it. But it does recognize counta. Here's a sample of my code. Private Sub UserForm_Activate() Dim MyUnion As Range Set MyUnion = Union(Rows(12), Rows(14), Rows(16), Rows(18), Rows(20), Rows(22), _ Rows(29), Rows(31), Rows(33), Rows(35), Rows(37), Rows(39), _ Rows(46), Rows(48), Rows(50), Rows(52), Rows(54), Rows(56), _ Rows(63), Rows(65), Rows(67), Rows(69), Rows(71), Rows(73)) txtTtlDays.Text = Application.worksheetfunction.countif(MyUnion, "&A") End Sub
View Replies!
View Related
Xirr Function In VBA
I am having trouble with the Xirr function in VBA. The problem revolves around the sensitivity of the solution to the 'initial guess'. specifically the boundary between a +ve and ve guess. i.e. if the xirr is large and ve a positive guess will always give me a solution very close to zero, as opposed to the more meaningfull solution say 65%. To get around this i have constructed the UDF below, which is used as part of a sub, however for some investments i get the type mismatch error (error(13)) even though the arrays are of identical size and the data should be uniform as it is a dump from a database. Has anyone encountered this 'type mismatch' problem with xirr before? is there a way to access the vba code for xirr so that it can be altered? Function fkxirr(arrVals, arrDates) Dim r As Double Dim Step As Double Dim tolerance As Double Dim xir As Double r = 10 tolerance = 0.000001 Step = 0.1
View Replies!
View Related
Workday Function Use In VBA
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$21,V2,Z2:Z$11) P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/enus/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.
View Replies!
View Related
Use Function/Formula Not Available In VBA
I want to evaluate Bessel functions as parts of a function in VBA. These functions are available in Excel not VBA. I tried to use range (). value to do this, i.e. Function Bessel2(x) Worksheets("Sheet2").Range("C1").Value = x Bessel2 = Worksheets("Sheet2").Range("C2").Value End Function In cell C2, I enter the formula BESSELI(2,C1). But this did not work.
View Replies!
View Related
SUMIF Function In VBA Macro
I have a macro[A] that sums a column based on parameters passed to it from another macro[b] Macro A Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant) Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange) End Sub ' Macro B Sub SumDebtorsStuff() Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp))) End Sub ...........
View Replies!
View Related
Calling A Custom Function Within VBA
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel. Public Function CreateFlexstring(Company As String, Cost_Center As String, _ Division As String, Geography As String) CreateFlexstring = Company & "" & Cost_Center & "" & Division & "" & Geography End Function
View Replies!
View Related
Writing VBA Macro Or Function
I have Asset Class in Column I and if it is 364AEL or 365AEL or 368AEL or 368BEL or 369AEL or 371AEL or 371BEL or 373AEL or 397CEL then 'POLES' assigned in to Owner Class (Column J). If Asset Class is 366AEL or 367AEL or 367BEL or 368CEL or 369BEL or 371CEL or 373BEL then assign UGCBL to Owner Class. Should we write the macro or can it be accomplished by any Excel Function?
View Replies!
View Related
Addin Worksheet Function In VBA
I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:  a range for the input data to smooth  a parameter detailing how to do the smoothing. It outputs a matrix of smoothed results, and therefore requires the CtrlShiftEnter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results. This is how the working syntax is in a cell: =SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH") And there the nightmare starts :  I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue  Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process). I have tried an example I found somewhere using a builtin Excel Worksheet function MMult and it worked with ranges: Sub SomeSub() Dim xArray As Variant, yArray As Variant, zArray As Variant Dim Fn As Object Set Fn = Application.WorksheetFunction xArray = Range("A1:B2").Value yArray = Range("D1:E2").Value zArray = Fn.MMult(xArray, yArray) ActiveCell.Resize(2, 2).Value = zArray End Sub
View Replies!
View Related
TREND Worksheet Function In VBA
I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25) I keep getting "Runtime error 1004  Unable to get the Trend property of the WorksheetFunction class".
View Replies!
View Related
Writing An IRR Function In VBA
way to write your own IRR function using VBA. Say if I call it Function MYIRR(cfs,per). Where cashflows occur in the periods specified.. i.e Periods: 0 1 3 4 6 Cashflows: 1000 300 500 100 200 I'm having trouble making sure the initial cash outlay and then cash inflows assumptions hold (problem with the /+ sign). I've been told by a friend to try and use abs() function, 'For..Next' loop construct inside a 'Do...While' loop.
View Replies!
View Related
