I need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
I'm trying to do, I already have a macro that takes certain cells in sheets in a workbook and copies to them to individual workbooks that are open.
So for example, I have a workbook with sheets A, B, and C. and then I have a macro that'll copy sheet A to an open workbook D and B to an open workbook E, and so on.
Problems I'm running into = if the workbook isn't open, it crashes, I'd like to have an if then statement where if workbook is not open, it stops?
Additionally, I'd like a for statement because there will be 10 or more sheets in total, so would be nice to have a for statement that changes the variables so For i = 1 to 10 where 1 would be D and 2 would be E and so on so it automatically changes the workbook names as necessary.
As you can see the letters repeat, and the dates are in order. Dates may repeat or be skipped.
I want to know how I retrieve the last date in the list that corresponds to a certain letter. For example, the last date that corresponds to "A" is 10-Jun-12. Similarly for "B", it is 11-Jun-12.
VLOOKUP will retrieve the first day.
In this case corresponding to "A": =VLOOKUP("A",A2:B12,2,FALSE)
Is there any way I can retrieve the last day with a formula?
My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.
What functions will allow me to get the item number information I want from Report 2 into Report 1?
I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.
I have data in a column (G) consisting of zero and ones. I would like to count the number of clusters of the number 1 in the data. For example in the data below there are 8 ones. But instead, I need to calculate how many groups of 1s occur. So in the case below the group of 1s = 3.
In terms of what defines a group. Whenever there is a zero either before or after the occurance of a 1 constitues a group, i.e the groups are broken out by zeros.
I have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'
So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)
I am trying to parse a section of html into excel but I don't know how to parse into a new row with each pattern rotation. I can do this both in Word and notepad++, but I can't find the same function of replace-with-line-break in excel's find-and-replace box.
Original html extract: "shb":{"landline":"£1","mobile":"£1","text":"8p","mms":"24p"},"af":{"landline":"18p","mobile":"18p","text":"8p","mms":"24p"},"ak":{"landline":"3p","mobile":"3p","text":"8p","mms":"24p"},"al":{"landline":"10p","mobile":"25p","text":"8p","mms":"24p"},"dz":{"landline":"5p","mobile":"27p","text":"8p","mms":"24p"},
Results I need: Capture.PNG
There is only one line as such in the entire page, so I don't need to loop through "blocks" of html codes with multiple lines.
I have had a look around and not sure what would be a good way to tackle this. RegEx (how to do it in excel macro)? HTML tags parsing (but these are not strictly tags)? I need to extract the correct section from page source as well, but I imagine this shouldn't be too hard. The url to the page I am extracting from is [URL] ........
I have a custom range I'll be copying from one sheet (a single entry registration form) that I'm adding to another (an "all entries" sheet) with a button assigned to a macro.
[Code].....
I then loop through the range, copying the cell from Sheet1 to Sheet2.
[Code]....
All works perfect, but I need to manipulate the data a little. If B3 from Sheet1 equals a certain string, I want to manipulate the data From Sheet1 B6 & B7 to paste into Sheet2 Column 1, otherwise paste B3 into Sheet2 Column1.
Again, my loop and everything works if I put B3 into Column1, B6 into Column2, and B7 into Column3, but it doesn't play well with the reporting I want to do later from this sheet.
Data Example
[Code]....
What I'm trying to do is if B3 <> "NEW TEAM" then put B3 (Joe Smith on Sparkles) into the new sheet column1 if B3 = "NEW TEAM" then put "B6 on B7" (Jane Doe on Fluffy) into the new sheet in column1 (where B6 and B7 are the strings, obviously).
I haven't figured out how, in my loop, to access the data in (myCell + 1) and (myCell + 2) while I'm on the first cell (myCell). I could do it by jumping back to the Sheet1("B6") and Sheet1("B7") but I'm trying to write reusable code, and this range might change, so I'm trying to be a little more flexible than hard coding in more cells.
I have 9 named ranges on worksheet Sheet1. I want to print every combination of every non-singular range on worksheet Sheet2. Below is a simplified version of the scenario.
There are three named ranges: Letters, Colors, Animals. Say the below are the entries for each range.
Letters = {A, B, C} Colors = {Red, Blue} Animals = {Dog}I want to print every combination of Letters and Colors but exclude Animals since it only has 1 entry.
Therefore my result would look something like this:
A Red B Red C Red A Blue B Blue C Blue
My thought is to make a multidimensional array GrandArray where GrandArray(1) = Letters and GrandArray(2) = Colors, then recursively go back through every combination and print to Sheet 2. I can set up GrandArray, but stepping through each element is creating mismatch errors.
I'm trying to avoid For loops since my real data has 9 ranges which may or may not be included in the final print.
My rows consist of the following cells, an author(s), an article name, a pubilcation name, a volume number, and a page number. There are thousands.
My first column has multiple names and need to separate the names into separate cells. Some names have a comma or semi-column separating them, some have the word "and" separating them. Most of the names are listed as last name, first name. (Some are not and I will have to deal wtih that later.)
Row 1: Eisenhofer, Jay W., Jordan, Kirk S. Tucker, Marc B."Business Faces Harsh Sentencing Penalties",Delaware Business Review, October 7-13, 1991951
Row 2: Embley, Craig M., Turezyn, Andrew J. and Welch, Edward P."Recent Developments in Delaware Corporate Law",S.A.S.M.& F., May, 19901443
I'm sure is a multiple part process. Is there a formula for placing anything separated by a comma into a new cell, or placing a tab between the names?
Ultimately this data will be placed in database from which to search for a name and article, etc.
I am trying to sqaure every element in a dynamic array and display the result . I donot understand how can I select the value in the cell using VBA?
Dim Y as variant, d() as double, i as long, j as long, rows as double, cols as double Set Y = Application.InputBox("select the matrix: ", Type:=8) Rows = UBound(Y) Cols = UBound(Y, 2) ReDim d(1 To Rows, 1 To Cols) for i = cols d(1,i) = ______==> How do I select the value of element in that particular cell and how do I sqaure it? I know cells(rowindex, columnindex) is used to select a particular cell but If I have a large array it would be difficult to go cell by cell and sqaure it.
how to get the table_array element of vlookup to either obtain details from a worksheet cell, or to enclose a variable (specifically part of the filepath) within the table_array formula. I have a template that gets saved and used in various directories, and I want Vlookup to to dynamically lookup information from a specific file that is also contained within the directory, without having to find and replace the directory name in the vlookup formulas.
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = .. 2 click - a2 = .. .... n click - an = ..
A friend of mine is playing a computer game, and he has to create 50 different potions to obtain her next goal. She would like to know if it's possible with excel to generate a list of potions based on 7 different elements. Here are the 7 elements : Black orchid, Cactus, Lotus, Pitcher plant, Rose, Tiger lily
Every elements could be repeated to generate one potion.
Therefore, the following combinations are valid :
Cactus - Cactus - Cactus Cactus - Rose - Cactus Cactus - Cactus - Rose Cactus - Rose- Rose
As long as there are no more than 3 elements, it's ok. I'm not looking for the precise coding, I just need to be pushed in the right direction...
I want to count the number of times any given number appears either as a consecutive group or singularly.
To give you a context I monitor windturbines and for any given fault code I wish to count the number of events it occurs in a month. Now it could be for 1 hour then clear the next then back for 17 then claer again. That would be 2 events!
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
I want to obtain from some elements the number of cells it takes to appear: We have for example A,B,C, and D, and they appear in the next order:
1A 2C 3D 4A 5D 6B 7C 8A 9A
What I want to know is how much last in appear each element.
1A1 2C2 3D3 4A3 5D2 6B6 7C5 8A4 9A1
For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.
My code should get the value of a hidden element from a form that is on a remote website. Then it should display that element's value on my excel worksheet.
One or the other of the 2 lines between the commented out lines should work. Instead the code runs until within a minute after the page has finished loading, and then stops completely without getting the hidden element's value.
Code: Sub work_damit() Dim ieApp As Object Dim URL As String URL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx" Set ieApp = CreateObject("InternetExplorer.Application") ieApp.Navigate URL
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....
I'm trying to populate a collection with PossibleAnswers to a Question. Every time I do this the values from the newly added entry get propagated to each entry in the collection (best way I can describe it - see code attachement).
Here's the existing code - including the current debugging code surrounding the add! The function is a Method of a Question object.
[Code] ....
All the Find and FindNext procedures do return the appropriate next value. However, the debug loop produces the following:
[Code] .....
As you can see, something is being added, but it seems to be affecting every single entry.
For some reason get a type mismatch error on when I try to determine the rth member of summation range. I have highlighted the relevant part of the code in bold. It is strange as I can obtain the address.
If ((All_nurse_names(r, 1) = nurse_name) And (All_status(r, 1) = status)) Then Debug.Print "test " & r & " " & summation_range(r).Address & " " & val(summation_range(r))
total_hours_in_shift = total_hours_in_shift + (summation_range(r)) End If
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.
So the definition is:
Dim Array(1 To 10) As UserType
and access to elements is
Array(5).Element1
The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.
I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.
I have some form used for "deret fauli test" it's like have 42 row cell and 37 column cell. What I need is count how much row between number 1to2, 2to3, 3to4, ... 19to20.
That mean from number 1 to number 2 have 42 + 2 = 44 row (this value put in A 45) number 2 to number 3 have 40(because 2 row have add to number 1) + 1 = 41 row (this value put in A 46) number 3 to number 4 have 41 + 42 = 83 row. (and this one put in A 47)