Evaluate A Count Statement With A Variable?
Jul 14, 2013
I am trying to perform a count statement in VBA as below, with the 1st Evaluate I get the correct response, however with the second option it shows a 0. The variable is obtaining the correct values as per the example of 6017Complete, this is proven with the MsgBox
VB:
Dim TillNo As String
TillNo = Target.Offset(, -1) & Target.Offset(, 0) & "Complete"
MsgBox "Till Number is" & TillNo
Target.Offset(, 10) = Evaluate("COUNTIF(C3:C123,""6017Complete"")")
Target.Offset(, 11) = Evaluate("COUNTIF(C3:C123,TillNo)")
View 6 Replies
ADVERTISEMENT
Apr 16, 2009
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies
View Related
Jun 9, 2014
I have a ss that has item descriptions, quantities and pricing.
Item descriptions are identified by a letter (a, b, c, etc) and in cells C20:c32. These are selected by drop down box. Item quantities are in cells E20:E32.
I want to evaluate cells C20:C32 and determine what letter is chosen. If A is selected in any cell C20:c32 I want to count the quantities for A in cells E20:E32. I can't quite figure out how to do this.
View 2 Replies
View Related
Aug 26, 2009
I am trying to use an If statement and wanting to evaluate another formula.
The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6.
Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
View 4 Replies
View Related
Aug 22, 2008
I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells
Sub Analysis()
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer
TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row
For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................
View 9 Replies
View Related
Feb 5, 2009
I cant see the wood for the trees! I have this formulae which works just how I want
ACO2 = Evaluate("= SumProduct((K2:K3=""Closed"") * (EG2:EG3=""06-15 Days""))")
However when I change this to a dynamic range I keep getting a syntax error. Can anyone see where I have gone wrong? I know its probably only a " or an ) out of place but its driving me mad!!!
ACO2 = Evaluate("=SumProduct(((Range("K2:K" & Lrow)=""Closed"") * ((Range("EG2:EG" & Lrow)=""60 Days+""))")
AC02 is an integer, as is Lrow (its a simple loop which works out how many rows there are).
View 3 Replies
View Related
Nov 15, 2013
I understand that the SumProduct doesn't have the same capabilities in VBA as it does in worksheet formulas. I am trying to construct it in an evaluate statement in VBA but the problem is I am using it in a userform, so the conditionals are passed from two combo boxes. Here is the worksheet formula
Code:
=SUMPRODUCT((Table1[Home]=C3)*(Table1[Away]=M3)*(Table1[Goals (H)]>Table1[Goals (A)]))
The values C3 and M3 refer to a cell known as "Home" & "Away". It's comparing to the two teams selected to a table with game stats to show wins and losses. The user form has a combo box list with those teams. Combo Box 1 passes to a variable known as hTeam and Combo Box 2 passes to a variable known as aTeam. I need those values passed through to C3 and M3. Here's what I constructed but get a type mismatch
Code:
Evaluate("=SUMPRODUCT((Table1[Home]=" & chr(34) & hTeam & chr(34) & ")*(Table1[Away]
=" & chr(34) & aTeam & chr(34) & ")*(Table1[Goals(H)]>Table1[Goals(A)]))")
View 4 Replies
View Related
Feb 27, 2014
I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...
View 1 Replies
View Related
Nov 23, 2011
I am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code
Code:
Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub
What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.
View 8 Replies
View Related
May 29, 2013
I need to know how to use a variable in an R1C1 statement. The statement is as follows:
Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion
Ideally the statement would read something like,
Set rng = Sheet1.Range("A2:Q10").CurrentRegion
How would I use a variable name like "FinalRowII" instead of a number?
View 8 Replies
View Related
Jan 16, 2007
Does the object in a 'With' have to be an object or can it be a variable containing the name of an object.
I want to carry out the same series of settings on a number of objects
and propose to define a Sub and pass the name of the objects as a parameter.
I then want to use a 'With' to set that object.
i.e. as shown below - though I cant see how it would work.
' combobox objects called Staff, Maker, etc
Call Setuplist("Staff")
Call Setuplist("Maker")
...
Private Sub Setuplist(whatlist As String)
'some complex filtering and sorting goes here
...
Set rnData = ActiveSheet.Range(whatlist+"List")
vaData = rnData.Value
With Me+whatlist ' this is the bit I cant fathom
.
.
End With
End Sub
View 9 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Oct 29, 2013
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
Here is my code
cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear
Set rs = New ADODB.Recordset
[Code] ........
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
View 1 Replies
View Related
Sep 14, 2009
Long story short: (I'm using the .."new" version of office - VISTA aka 2007)
Column A has text
Column C has numbers
I am trying to find some way of checking Column A for certain text, then adding the values in Column C that are on the same row as the values in A.
Example:
A B C Course Started Finished GTA 16 13 GTA 16 15 GTA 16 15 CA 16 12 GTA 15 13 GTA 16 15 JS 21 21 JS 16 12 GTA 15 15 NT 7 6 NT 8 7 GTA 16 12 CA 16 12
I'm looking to return a result of 98 people who have completed the GTA course
so it finds the word 'GTA' in column A, then moves across and counts the value entered in column C - then adds the total
i currently have this as the formula:
=COUNT(IF($I$15:$I$137,"JF",)*SUM($K$15:$K$137))
View 9 Replies
View Related
Apr 4, 2012
I'm using this formula to give me an average. some sheets may be blank and some will have results.
=IF(COUNT(WCMHT!B5+LICMHT!B5+SCMHT!B5+CAMHS!B5+Crossroads!B5+UCMHT!
B5+ADU!B5+IEDS!B5+SPS!B5+'SMHOPS CONSITON'!B5+'SMHOPS NOWRA'!
B5+'THE JUNCTION'!B5+FERNHILL!B5+WFT!B5),AVERAGE(WCMHT!B5+LICMHT!B5+SCMHT!
B5+CAMHS!B5+Crossroads!B5+UCMHT!B5+ADU!B5+IEDS!B5+SPS!
B5+'SMHOPS CONSITON'!B5+'SMHOPS NOWRA'!B5+'THE JUNCTION'!B5+FERNHILL!B5+WFT!B5),"")
So far only two sheets have results. 40% and 60%. therefore this formula should show 50% as the average because all other pages are blank ("").
Problem is it returns 100% as the average. What can i add to this formula?
View 3 Replies
View Related
Jun 3, 2014
Trying to write an if statement giving a simple value of 1 if a time is outside of weekday hours 8am to 9pm. Cell formatted as (6/3/17 2:15 PM). Function should report "0" value as this is a Tuesday between 8am and 9pm.
A cell formatted as (6/3/14 7:54 AM) should result in "0" value.
View 6 Replies
View Related
Jun 14, 2009
i am using the IF script, and i am also using the COUNT script in the logical part of the IF statement. i need to make sure that both K3 and V3 have a value inside of the cell in order for the statement to work, although when just K3 has a value, the IF - TRUE statement works anyway. any ideas on how to fix this? =IF(COUNT(K3,V3),SUM(K3+V3),"")
View 4 Replies
View Related
May 20, 2009
I am trying to find the number of unique 3 variable combinations there are in my rows of data. I have tried the frequency function but must be missing something.
Attached is a sample of what I am trying to achieve although there may be a better way of portraying the data.
View 4 Replies
View Related
Oct 1, 2013
Every week I get a file download from SAP with multiple tabs and on each tab are variable number of rows (can by 10 or 20 or 200, doesn't matter). I recorded a macro to add additional information that is required for the team prior to discussion each week (namely column D, E, K and O).
I am not experienced w VB but I know there has to be a way to account for the fact the rows vary each week. See my code below (pasted only a portion covering a couple tabs but you can see in the first part how it was 11 rows this week. Next week could be 25 rows. So how do I properly reflect that in the macro?).....
VB:
Sheets("BPTO").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],css,2,FALSE)),(VLOOKUP(RC[-2],css,2,FALSE)),(VLOOKUP(RC[-1],css,2,FALSE)))"
Range("E2").Select
[Code] ......
View 1 Replies
View Related
Jul 11, 2014
I am working on a set of Books to be published together. I am attempting to count the number of times a Book contains a glossary word that only appears once in the whole series. Conceptually I need to check if a word in A1 == any word in Sheet2 A:A, if so then does the value in Column B == 1, if so increase count by 1; if not proceed to A2 == Sheet2 A:A, and so on.
If this were a basic program I would do as follows:
C==2 **place count
s==size(A:A) ** check for length of column A (which I know is 179)
if C<=s
match (A'C', Sheet2 A:A)
if B'C' = 1
COUNT == COUNT + 1 ** COUNT is the number of times a book contains a word that only appears in that book.
Else
[Code]...
Here is a sample set up
Screen.jpg
View 5 Replies
View Related
Apr 3, 2013
I have a workbook with multiple sheets. On each sheet is different sets of sales person data. Each set consists of a sales order number, and the type of product that was sold.
I need a count of all the distinct product types that each sales person sold.
I need the solution in VBA, as i need to run the macro through all the worksheets. The size of the range for each sales person differs.
I have got all the VBA done, except for the part where i specify the FORMULAR1C1 for the specific cell, to do the distinct count.
View 1 Replies
View Related
Sep 17, 2009
I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.
I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.
=COUNTIFS(Extract!B:B,"
View 9 Replies
View Related
Apr 25, 2007
I have a workbook with several sheets, some are permanent and then there are certain sheets whose quantity varies depending on how many sub-contractors we use on a given job. The workbook starts as a template and there are 10 sub-contractor sheets to start with, if we only have 5 subs then the last five sub sheets are deleted. The sheets start out with the tabs named "Sub 1" "Sub 2" etc. but after the book is set up the sheet tab names are changed to the sub name. I've included some partial code below, with the goal being to run code on the sub-contractor sheets no matter the tab name or how many there are.
So the workbook has 5 "non-sub" related sheets, I was thinking that if I could do a sheet count -5, and get that number into the array it would accomplish what I want to do. And if someone added a new sheet it would still be processed with the others.
Public Sub SubConEstNum()
On Error Resume Next
Dim S As Worksheet, names As Variant, i As Integer
names = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
For i = LBound(names) To UBound(names)
Set S = Sheets(names(i))
S.Range("T3").Value = S.Range("S3").Value
S.Range("T1").Value = S.Range("S1").Value
S.Range("T2").Value = Sheets("Bill").Range("K1").Value
Next i
End Sub
View 3 Replies
View Related
Apr 4, 2009
array variables:
How can I count the number of elements of a particular dimension of an array variable that have actually been filled with items/values?
For example, the array variable in this procedure has two dimensions. Dimension 1 has three elements, and dimension 2 has 5 elements. I then add values to some, but not all the defined elements of dimensions. How can I count, for each dimension, the number of elements that have values rather than are empty?
Option Base 1
Sub test()
Dim ExampleArray(3, 5)
ExampleArray(1, 1) = 3
ExampleArray(1, 2) = 4
ExampleArray(1, 3) = 2
ExampleArray(1, 4) = 6
ExampleArray(2, 1) = 5
ExampleArray(2, 2) = 9
End Sub
View 9 Replies
View Related
May 22, 2007
I have a form using text boxes to enter some data into a spreadsheet. These text boxes appear in pairs for any single point (e.g., name ; location). Does anyone have a code which can actually define how many of these pairs will appear on the form when loaded? For example if there are 5 points to be defined the form should have 5 pairs of these text boxes. The maximum number of these pairs is around 10 and I could make a form with a 'standard' number of 'pairs' but that's not really it. I've tried to attach the form but it did not work
View 9 Replies
View Related
Nov 17, 2012
I have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
View 1 Replies
View Related
Apr 27, 2009
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies
View Related
Jan 26, 2008
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
View 9 Replies
View Related
Mar 25, 2014
I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.
I have Column A with random positive numbers.
I have Column B with random negative numbers.
I have Column C with random numbers.
I have Column D with random numbers.
I have Column E with random 0’s and 1’s.
I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).
I have been able to obtain the values in 2 cells within a fixed range:
If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)
If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)
Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.
Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)
However, that only works for a fixed range and continuous updates are made.
Book1.xlsx
View 2 Replies
View Related
Jan 25, 2010
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 give me an error.
View 9 Replies
View Related
