A Function For Calculate The Cells Takes To Appear Each Element
May 15, 2008
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.
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I have a cell, lets call it A1, w/ an IF statement referencing another cell, which is a function of two values taken from RTD. A1 is either blank or reads "One." I want another cell to tally the # of times that A1 takes on the value "One."
The issue: I've tried approaching this by using Worksheet_calculate and an IF/Then statement to add 1 each time Range("A1") = "One" however this doesn't work because it keeps adding 1 while A1 reads "One." The calculations seem to fire off every milisecond, so if "One" flashes for 0.5 seconds, I get 500, and if it flashes for 3 seconds, I get 3000. For both cases, I'd like it to just add 1.
I have a very long if function that takes a value froma cell and then totals the row up to that point.... Is it possible to make this shorter? I have tried to put a sum function in there but that also comes with the nested functions have been exceeded error message...
I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.
I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "N:N" Dim Cmnt On Error Goto ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Row > 3 Then If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0 End If If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39 End If If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6 End If.....................................
I am trying to create a macro that can take the average of the the first 24 cells within a sheet, place the answer onto a cell in the next sheet (e.g. sheet2 in cell A1), then go back to the previous sheet, take the average of the next 24 cells within the sheet and paste the average of this new set in A2. I want to create a loop that will do this 365 times.
I have only managed to create the following code, however its only obtainning the average for the first set of 24 cells starting from B6 in sheet 1. I dont know how to use offsets that well....
VB: Sub Oval1_Click() For i = 1 To 365 Sheets("H1 - Riser Turret pressure").Select Range("B4").Select ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[2]C:R[25]C)" Range("B4").Offset(1, 0).Select Next i End Sub
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 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 have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.
The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.
I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.
I have a spreadsheet that keeps attendance. Now I need to calculate the units (1 unit= 15 minutes) the children are actual here. On a normal schedule they are here for 16 units but if the are late the units get subtracted. When entering the data, they would like to use letter instead of numbers.
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.
I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1 Dim i As Integer Dim j As Integer Dim counter As Integer Sub General() 'Makes general calculations Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý Dim FIRR(49) As Variant 'Yearly FIRR Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet End Sub
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
