I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10
In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")
Hoja1 ABCDEFGHI1J J D 1 22G J D 0 Spreadsheet FormulasCellFormulaG1=SUM(COUNTIF(INDIRECT(ADDRESS(ROW(),1)),"J"))I1=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"J"))G2=SUM(COUNTIF(INDIRECT(ADDRESS(ROW(),1)),"J")) Excel tables to the web >> Excel Jeanie HTML 4
Its not work =SUM(COUNTIF(INDIRECT({ADDRESS(ROW();1);ADDRESS(ROW();3);ADDRESS(ROW();5)});"J"))
I have a spreadsheet that I have developed at work to track sales related data. As part of the reporting for this data, it is sorted by date. I have been asked to capture some additional related data and due to the structure of the spreadsheet(that has been in use several months with no issues) I can not put the additional data in adjacent columns without doing a complete redesign of the format.
I can place the data several columns away.... The data "in the middle" is static and is used for other calculations and should not be sorted. Is there a way to sort both ranges of data WITHOUT disturbing the columns separating them and keep the proper relationship with the data?
See attached for a very simple example. In this data set the "original" data was in range A2:E11 the "New" data is in Range I2:M11. It would make life simple if I could do something like this (but it sorts everything in between)
how to Determine two uncontinnuous ranges are same? "Address" can not work here.
Sub Macro1() Set Rng1 = Range("H1:H10,C1:C10,F1:F10") Set Rng2 = Range("F1:F10,C1:C10,H1:H10") Debug.Print Rng1.Address Debug.Print Rng2.Address If Rng1.Address = Rng2.Address Then Debug.Print "yes" Else Debug.Print "no" End If End Sub
I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous. For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5". I’ve tried a few different things but I can’t get the function to work.
I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:
Sub Ny_uke() ' copy cells Range("B21:K50").Value = Range("AX21:BG50").Value Range("B54:K83").Value = Range("AX54:BG83").Value Range("B87:K116").Value = Range("AX87:BG116").Value Range("B120:K149").Value = Range("AX120:BG149").Value Range("B153:K182").Value = Range("AX153:BG182").Value Range("B186:K215").Value = Range("AX186:BG215").Value Range("B219:K248").Value = Range("AX219:BG248").Value ' clear content of cells Range("AB6:AS12,B21:K50,B54:K83,B87:K116,B120:K149,B153:K182,B186:K215,B219:K248").Clear End Sub
When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.
So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way:
the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...
Sub Replacing() Dim rRange As Range Dim lArea As Long Dim Co As Byte Dim NaCo As Byte NaCo = 99 Set rRange = Range("B:C,E:F,H:I") With rRange For lArea = 1 To .Areas.Count With .Areas(lArea) Co = Choose(lArea, 1, 2, 3) .Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With Next lArea End With End Sub
the problem is that it's not working for Office 2000
This time I want to replace: number "99" in range("B:C") to number "1"; number "99" in range("E:F") to number "2"; number "99" in range("H:I") to number "3";
Sub Replacing() Dim rRange As Range Dim lArea As Long Dim Co As Byte Dim NaCo As Byte NaCo = 99 Set rRange = Range("B:C,E:F,H:I") With rRange For lArea = 1 To .Areas.Count With .Areas(lArea) Co = Choose(lArea, 1, 2, 3) .Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With Next lArea End With End Sub
before sort: ---A-B-C--D-E-F--G-H-I 1-99-5-6-99-1-2-99-3-4 2-99-6-5-99-2-1-99-4-3 3-99-7-8-99-5-6-99-8-9 4-99-8-7-99-6-5-99-9-8 etc
after sort: ---A-B-C--D-E-F--G-H-I 1-99-5-6-99-1-2-99-3-4 2-99-5-6-99-1-2-99-3-4 3-99-7-8-99-5-6-99-8-9 4-99-7-8-99-5-6-99-8-9 etc
Sub SortNoncontiguousRanges() Dim rRange As Range Dim lArea As Long '10 rows in columns B and C '10 rows in columns E and F '10 rows in columns H and I Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange For lArea = 1 To .Areas.Count With .Areas(lArea) .Sort Key1:=.Cells(1, 1), _ Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight End With Next lArea End With End Sub
I use to have a macro that could copy formulas from non contiguous ranges and paste the values in the same cells. So for example, I could select a non contiguous range like B5:B10, E10:F12, and G1:G10. The macro would then copy and paste the values in those 3 ranges, so those 3 ranges would be values instead of formulas.
I think it worked by storing each range and then looping through each range to copy and paste special values.
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
I transposed them into a separate sheet and used that for my lists. I have to share the file with macs.
My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.
Solution (?): if I make the combobox lists programmatically, it will work on the macs?
The UserForm1 with 3 comboboxes: The comboboxes are not dependent on each other. For each combobox, I need to have 2 columns. For each column, I need to transpose 2 x ranges (they are NOT contiguous)
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A100")) Is Nothing Then Target.Font.Name = "Marlett" If Target = vbNullString Then Target = "a" Else Target = vbNullString End If End If End Sub
How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant Dim Denominator As Double Dim Numerator As Double Dim i As Long Dim Fcst As Variant Dim Act As Variant Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size") If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.
I'm attempting to use the countif formula to make sure a number is entered only once in 4 nonconnected ranges, I492:I496, I499:I503, K492:K496 and K499:K503. I've tried everything I know and as I'm self taught and my teacher is an idiot, I'm not having any luck.
Countif: Is there a way to have a single criteria (a persons name) and multiple ranges example: a6:a10 c6:c10 and recieve the sum of that criteria and ranges?? I know there is I just cant get it.
I need to count a persons name entered in multiple ranges (cells or areas) on the same worksheet. I cannot make one big range because i will need to do the same for the b6:b10 but for a different "need" the a colum and c colum bieng "completed" the b colum bieng "not completed". I have tried =countif(a6:a10 + c6:c10, cell_with _persons_name) and for obvious reasons that wont work,
I am using a work sheet where I want to count if Column A has one criteria and column B has another example column B states is used to track contracts it can be vendor column L tracks status it can be open, received, or closed
I want to count the cell if the vendor name matches and the status is open
also if that is possible is the same possible with 3 ranges and criterias?
Report X contains 2 relevant columns... E contains the date created for each item, J contains the type of each item - in this case i wish to see how many items '2' there are in the last 30 days
Report X is a daily report copied into the same tab each day - so the data will change every day depending how long each item is still in the system
this formula does work in the example sheet i provided but not in the spreadsheet in which i need it to work!
i also have similar formulas to calculate how many items are present in between 30 and 60 days exist, and how many over 60 days - all return the value "0"
column E containing dates is in the format 01/10/2008 - changed using Ctrl F - replace to amend it from 01.10.2008
How to use COUNTIF when there are multiple criteria. For eg. I have 3 columns, and I want to count the # of employees in each row if all 3 criteria in columns C, D, and E hold true.
I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
I want excel to count the number of items in a range that I have named "Name" and I have another range that I have name "date" which contains (obviously) dates. The dates are in order. I want to count the number of items in "name" that are associated with the date in the "Date" range.
The problem is I want to count the names in a date range, which is todays date through to 30 days after. I have to days date already posted automatically in K1 [by the formula =TODAY()]
Essentially, count all the times "Bob" appears in the Range "Names" that appear in the next 30 days. My Brain hurts just trying to describe it