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 3 RepliesHow 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
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?
I am using the COUNTA function which works great but I also have data that I dont want counted and do not know of the problem solver.
E.G
in B1 I have vlookup function thats brings back either "B", "C" or "M".
in c1 I have =IF(A1="C",B1,"")
Now, that tells me if the data is B or not. If it is, it says B If it isnt, its blank
Thats great but when im trying to count how many "B"'s there are, it counts the blanks as well!
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.
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
MAX function with a variable range. I want find the highest value in a changeable range in a column. The problem for me is, how can I automatically change the range where the highest value is returned.
Here is an example:
Cell A1: 14
In cell A1 is the number entered that specifies the range. In this example the range is 14 rows =MAX(A18:A31).
A2 126.36
A3 126.16
A4 124.93
A5 126.09
A6 126.82
A7 126.48
[Code] .....
Using =MAX(A18:A31) returns 128.57. So far so fine.
But what is the MAX function if the range value refers to the number entered in cell A1? If I change the value in A1 from 14 to 20 how can I make the MAX function flexible that it refers to cell A1 as the range value?
Using the value 20 in cell A1 the MAX function would be =MAX(A12:A31). I can change this manually of course but I want a MAX function that refers to cell A1 as the range value.
I want also mention that the data series is update every day, so that each day a new value is added in column A, e.g. A32, A33, A34. and so on.
I would very much like to sum a range of values using variables instead of hardcoded ranges. I have tried the following (and variations of )
Range("D2").Formula = "=Sum(Cells(rowIndex1, colIndex1), Cells(rowIndex2, colIndex2))"
where rowIndex1 is starting row number, colIndex1 is the starting column number.....
It does not work.
I need to calculate the max over a range of figures in a macro.
The "from" is a variable, call it X
The "to" is my counter in a loop so (Cells(-(Counter - 1), 5)
What I'm trying to do is to tell Excel that I want the max from X to the current counter cell.
e.g. Application.Max(Application.Index(X, 1):Cells(-(Counter - 1), 5))
but this gives me an error. I also attach a spreadsheet.
I recorded this macro based on a column with a drop down window in which data has been filtered .....
View 12 Replies View RelatedHow should this read? So SheetName is the variable in the range funtion?
Dim SheetName As String
SheetName = ActiveSheet.Name
Set AllCells = Range('" & SheetName & "'!B7:'" & SheetName & "'!B60000")
I have an excel based report where I drop data into a blank spreadsheet each month and my formula (in a second worksheet) finds the relevant information. I do this by using the Match function on the first row of my source data. This finds the column numbers to the data I am interested in. (This is handy as additional columns of data appear almost every month). Most of my formula (in the second worksheet) works on vlookups using the column number from the match function.
However, I need to do a sumif. Sumif requires cell references rather than a column number. How can I get around this? Due to the nature of the report, VBA isn't appropriate in this instance. I'm only producing the initial report; someone else will be updating it each month.
How do I pass MATCH or COUNTIF the current dynamic row instead of hardcoding $3? The column is fine hardcoded. Column F has data validation allow List with source equal to dynamic range "userlocation". Column H has data validation allow List with source =OFFSET(userlocation,MATCH($F$3,userlocation,0)-1,1,COUNTIF(userlocation,$F$3),1)
In Column H, this will in effect provide me with a list of values based upon the value of cell F3. Problem is I can't seem to figure out how to reference ROW as a dynamic reference in my MATCH and COUNTIF function.
EX
Column F Column H
Screen1 (if Screen1 = value in my dynamic range pass me back value in a list)
Screen2 (if Screen2 etc.
Screen3
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 5 Replies View RelatedI sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).
So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.
Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.
I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedI am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
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.
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.
I am using the following formula to calculate the number of responses:
=COUNTIF([Cleanliness of Environment:],"None of the above")
However, I only want the number of cells which contain words other than "None of the above". The formula is counting empty cells. Is there a way to weed out the empty cells and receive a tally of only cells with words other than "None of the above" in them?
Code:
Private Sub custcountTB_Enter()
custcountTB.Value = Sheet5.Columns(1, 1).FormulaR1C1 = "=COUNTA(R[-4]C[-3]:R[95]C[-3])"
End Sub
All I am after is to count how many customers there are and show the value in text box.
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
How do I count the number of cells in a range that has text, even though all cells has a link....
View 9 Replies View RelatedSay 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?
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.
Is it possible to use a variable in Countif?
See attached sheet. When a number repeats for the 3rd time, I need to reset the
Countif function.
For example;
Countif($A$5:A20,A20).
I need to change the $A$5 to the current row number so we can start the count again. I need to do this several times during the sheet.
We are counting numbers until they repeat for a 3rd time and then we start the count again at that point.
I'd like to compare values from different sheets. My sheets contain data from different years. I have sheets named 2000, 2001, 2002, 2003.... I also have a sheet named "compare". Now I'm using functions like: =('2008'!J13/'2007'!J13)-1
which gives me the relative change between years 2007 - 2008 in the cell J13.
I'd like to use the sheet name as variable. For example: "year 1" is given in cell A1 and "year 2" given in cell A2. Then I could enter (for example) 2007 in cell A1 and 2008 in cell A2. How can I use these cell values in the function? I’ve tried something like: =('A2'!J13/'A1'!J13)-1. I assumed that 'A2'!J13 would be the same as '2008'!J13. It didn’t take me long to figure out that doesn’t work. What would be the right way to do this?
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?