I have a function that I call with the name of a shape that exists on my worksheet.
I actually select that shape on the worksheet, then execute a macro and since it is a single shape selection, it is easy to get its name:
I have a column of values 2000 rows deep, and in separate column would like to determine the relative magnitude of any given value compared to the rest of the values in the column. So for example, if the highest value is 23.3, the number that would be returned is 1 (meaning this is the max value), if the lowest value is 12.4 the number that would be returend is 2000 (meaning this is the min value); if the value is the 12th highest, then the the number that would be returned would be 12 and so on.
View 2 Replies View RelatedIm trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.
Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.
I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).
User can make a single or a multiple selection in the country slicer.
So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")
I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")
Now, is there any way to loop this within the array, how many cells it should take?
For i = 1 to ..
code from above
Next i
won't work because then it only takes the last value, so I kind of have to loop it within the array.
I am using a VLOOKUP formula, but when I drag it down it doesn't keep the same array selection. Is there a way so that I can drag it and it relates to the cell to the left, but the array selected stays the same. I don't really want to type the same thig out for every cell as there are 6000. I am using: =VLOOKUP(D2,Working!A2:C23,2,FALSE) and want D2 to change accordingly but for working!A2:C23 to stay the same.
View 2 Replies View RelatedI've got a formula which makes a word "Order" (column B) meet the closest value of 35.00 (column A)
How to modify the formula so that ''Order" meets the closest value of 35.00 in the range (>=35;<39) throughout the column?
There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".
I've been trying to change the value comparisons and precisity (pecentage) to set up the range from 35.00 till 39.00.
but encountered a problem that "Order'' often meets two closest values of 35 till 39, often one of them is going under 35.00 e.g. 36.50; 34.00.
Consequently how to change/substitute the formula, parameters, value comparisons etc. to meet the requirements!
See the workbook attached. 13_05_det_closest_value.xlsx‎
What code can i use to determine the same rows in 2 different columns and compare the data in those two cells?
View 9 Replies View RelatedI have a document in which I am trying to determine the length of time between a start date & time and a finish date & time. The format of the time/date cells is
mm/dd/yy hh:mm:ss
Basically, I am just subtracting the first cell from the second. This works fine as long as both dates are in the same day, such as
START FINISH ELAPSED
08/20/14 23:42:22 08/20/14 23:43:59 0:01:37
However, if I have a situation such as
START FINISH
08/18/14 23:00:15 08/19/14 0:03:22
the ELAPSED cell fills with ######## with a tooltip stating that "dates and times that are negative or too large display as ######." The actual result in this example should be 1:03:07.
How do I get consistency amongst my resulting formulas?
I am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.
As attached.. TEST PSR.xls‎
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
I try to do the following I get an error 'Subscript out of range'
View 3 Replies View RelatedI am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?
There are a lot of worksheets with a lot of similar data from which I have to determine the threshold. So if possible I would like to create a macro.
I want to know the threshold value when Y=2.
The threshold can be between different Input Values (=Stimulus Intensity (µA)).
For example: the threshold of amp P2-N1 & P3-N3 lies somewhere between 200 and 252µA. The threshold of P1-N1 lies somewhere between 318 and 400µA.
Is it possible to determine the threshold value with a formula?
The table I'm trying to query contains the beginning and end date the data is effective. The end date seems to always be either 27 or 34 (4 or 5 weeks) after the start date.
This is the table I'm trying to query:
[URL]
I want to have it query the whole history going back 2 years, and be refreshable by other users on demand.
If I put in dates that do not match up exactly with the start/end dates they have set, I get something like this:
[URL]
I've got a plan of how to approach the data extract, but how can I determine whether or not the web query found a specific table (and should therefore try a different date offset)?
I am putting together a spreadsheet and I want to loop through a series of columns (G to L let's say) and in those columns I want to look at a range of rows (4 to 17 let's say). And if that range has no values in it, I want to hide that column and then move on to the next column. I am having a bit of trouble figuring out how to determine if the range is blank and then building that into a loop.
View 5 Replies View RelatedI've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
I have a situation where I have a column with 10 numbers in it... say A1 - A10 each contain an equation that will solve to a numerical value. If one of these does not solve and gives me a "#VALUE!" response, how do I tell a function to look for that? I'm specifically attempting to use Match to find it, in this case "=MATCH("0",A1:A10,0)".
View 4 Replies View RelatedIn cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps)
I was able to pull the Quarter and year (Q2 2007) using...
A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 Replies View RelatedHow can I determine if the user selected "disable" at the initial macro prompt screen.
Basically, I would like a formula/function that can say whether Macros are switched on or off by the user.
I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.
Unfortunately that much is what it is and is beyond my control, so I just have to work with that.
What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?
I want to determine the range in the countif function automatically and relating to a date (i.e. today).
In the attached sheet there are two employees who worked during a certain period, a day worked is 1
Then I would like to count how many days each employee has worked up until today, counting the 1's in the row of that employee until today.
Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies View RelatedI have set the default in Excel to move across to the next cell after I press Enter. This is fine for most workbooks but I have a particular workbook in which it would be preferable (save keystrokes) for the cursor to move down one cell. Is it possible to have one action as the default but another for a particular workbook?
View 7 Replies View RelatedI have a list of data that is the day of the month(1-31) that an event has occurred. (sample data in attachment) I want to find some way to find when the "hottest" time of the month is when the events are occuring.
how to get the end result. I think for myself a chart of some sort would be good to see visually, or some kind of statistical breakdown.
My only requirement is that the list is always being added to, so the end result needs to be updating with the complete list.
Im not an expert in excel, so the more details on how to do this the better. Even better would be creating it in the attachment, and uploading your version so I can see exactly how you did it.
I am going to be taking this idea and apply it in the same way for day of week (1-7) and time of day.
I'm working on this sheet and I can't figure out how to do it.
I'm trying to determine the variable "selling price" on the basis of given values, buying price, Net profit and Expenses.
Currently using "=IF(CC3=CF3,1,0)"
I would like to loosen this up by +/- 1.5% of the difference between the two cell values would produce a positive result.
In an error handling procedure, I want to be able to capture the name of the Module and Sub procedure (macro) that the error has occurred in.
Is there any way to do this, outside of storing the name of the Sub somehow in a variable?
The Err.Source method seems to only contain very broad information, such as "VBA Project" when an error is encountered.
I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?
View 9 Replies View Related