I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?
View 9 RepliesI want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
I have created some user defined functions and have associated them to a custom category. To simplify things, I wanted to add these functions and the custom category macro to an Addin I already have installed for all my employees. How do I get the the custom category macro to excecute? It should run as soon as excel is opened...but I am not sure what event should be used and where is it installed?
View 2 Replies View RelatedI am trying to create a custom function to act much like the database capabilities of excel in summing a column based upon multiple criteria of other columns. First, before I get too far, does anyone have an already built custom VBA function that will accomplish this? I find that using the built-in database functionality in excel is VERY slow!
Anyways, I am trying to pass an argument that contains multiple ranges (much like you can do with the SUM() function):
=MultipleSUMIF("E11:E14, F11:F15", "A1, E1", G11:G14)
The first set of arguments is the ranges that I need to look through. The second set of arguments is the values that I need to search within the first two ranges. The third argument is the column that I need to perform a SUM function on.
Instead of doing this, how can I create a function that will allow me to say, "Add another criteria range/value" and it will add another criteria to the list. I am currently passing a string, and that is not ideal, obviously.
Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:
Multiple substitute or replace text using a table
I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:
Function SuperSub(OriginalText As String, rngOldText As Range)
Dim cel As Range
Dim strOldText As String, strNewText As String
' loop through list of old_text used in substitute
For Each cel In rngOldText.Cells
strOldText = cel.Value
strNewText = cel.Offset(0, 1).Value
OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
Next cel
SuperSub = OriginalText
End Function
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.
The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst)))
If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"
Here is the macro:
Sub Import()
'
Sheets("Shoebuy FTP").Select
Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again.
It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
I am trying to create a custom button in Excel that will sort on two different columns. One column has $ Sales and the other has Unit Sales. So you can click on the button and it will sort by $ Sales and if you click the same button again it would sort by the unit sales. I'd also like the text on the button to reflect what option the data would be currently sorted by. I am able to do one of these macros and assign it to the button, but can't figure out how to get both sort options assigned to the same button. I have had outside people send spreadsheets to me with this but can't figure out how they did it.
View 2 Replies View RelatedI have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
I have a spread sheet that contains many user defined functions from a 3rd party provider that downloads financial data from an external database. Often not all of the data is required by the user, and as the UDF's take a long time to calcuate, i'd like to provide the ability to easily switch them on and off. Originally I had been looping through cells and either adding an apostrophe in front of the = or removing it. However running this loop is very slow.
I found this thread
(Optimize VBA Loop For Inserting Formula)
and adapted it to convert formula strings (with ' in front) to formulas in a single pass by setting:
myRange.formula = myRange.value. This works very well. I can't, however, work out how to go back the other way in a single pass. Have tried: myRange.formula = "'" & myRange.formula. This works for individual cells, but not for more than one at a time.
Assume I have a sheet consisting of 50 rows with 3 columns of figures. A fourth column contains a formula to calculate values, eg A1+B1+C1, on each line/row. I could enter A1+B1+C1 in the first cell and Copy, Paste to the other 49 cells. Alternatively, I replace A1+B1+C1 with a UDF.
Is there any difference in performance with using a UDF compared to using the long-hand/standard approach, or is it just a question of convenience? After all, both are doing the same operation with the same calculation.
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
I want to calculate the following...
If Cell A>40, Then the result should be ((Cell A-40)*23.25)
If it is not >40, Return 0.00
This is for a timesheet / overtime.
Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:
View 4 Replies View Relatedi have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.
View 7 Replies View RelatedSo I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.
To visualize, i just want to add up the students that have not attended a class.
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type
Sample file attached.
I am trying to write a formula that will satisfy the following:
C1 and G1 are number values.
IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.
I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.
View 4 Replies View RelatedCurrently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).
Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
= left(worksheet name! row(1) column.range("startno"),2
I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.
Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.
The file I am working with is available here
(1) I need to work out the value of 2 years worth of interest on a loan - details would be
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months
I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...
the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)
The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...
(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...
what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee
There are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this
{=IF((G4<>0,F4<$g$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>$g$1="On Target")+(F4>=G4="On Target"))}
I'm trying to find a function for the following statement:
=number of rows where columnA="value" AND columnB<ColumnC
So this formula would return 2 from the below table if the "value"=5...
X A B C
1 5 5 6
2 5 8 9
3 1 6 7
4 5 9 8
5 5 9 9
IF/OR function below is not working
=IF((OR(B14=1,2,3,12,15,16)),"A",IF(OR(B14=4,5,6,7,8,9,10,11,13,14,21,40,41,42,43,44,45,46,47,50,51, 52,53,54,55,56,59,60),"B",IF(OR(B14=20,30,57,58),"C",IF(OR(B14=33,31,32,34,35,22),"E F OR G",IF(OR(B14=36),G,"ERROR")))))
I tried to do a search for this but the search function was not working.
I have an excel sheet that I can scan Mac address into. The reason I created the sheet is because when I scan them in they are uppercase and do not have separators. I would like to combine 2 excel functions into 1
This one
=LOWER(C2)
and this one
=LEFT(E2,2)&":"&MID(E2,3,2)&":"&MID(E2,5,2)&":"&MID(E2,7,2)&":"&MID(E2,9,2)&":"&MID(E2,11,2)
I am trying to add up a specfic column in Excel based on the parameters or two different ones.
I.e If Col B is blank and Col D is 03 then add up the value in Col P
But I can't seem to work out how to do it, I have tried conditional sums and everything else I can think of, but it is still not working.
is there any way I can make the chart inputs variable. I have an array of numbers. lets say 100 of them, these are linked cells and depending on the input I might have less than 100 numbers in these 100 cells. All the cells which have numbers will appear in a block first and all the non number cells will appear below them as blanks in anotherblock. Now i have to create a chart from these numbers, so at times I have to take the 90 cells where there are numbers and other times I have to take 95 cells. Is there any way I can get over this.
View 9 Replies View RelatedI'm trying to write a macro that looks up Column A so that each time it finds "Agent" as the first word in a cell, it takes that whole cell and copies it to another sheet.
I tried creating an array function that combines the If, Vlookup, and Right() and Left() functions, but no luck. Ideally, I would want this as a VBA macro.
For the odds range, i have already two cells(the upper(F9) and the lower(E9)) and i have a cell named the current odd(G9). What are the formula together for the attribut cell(H9) for the following data:
1.0 : The current price is outside and below the odds range spread. For example, if the range is between 2,00 and 2,20, the range’s attribute will be 1.0 if the current price is below 2,00
NOTE: Obviously the formula for this one is =IF(G9
1.1 : The current price is on the bottom edge of the “odds
range” figure. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 1.1 if the current
price is 2,00
NOTE: The formula for this one would be =IF(G9=E9,1.1)
1.2 : The current price is inside the “odds range” spread
and is placed one or two clicks from the bottom edge. For
example if the range is between 2,00 and 2,20, the range’s
attribute will be 1.2 if the current price is 2,02 or 2,04
NOTE: I have a hard time creating a formula with this one.
2.0 : The current price is outside and above the “odds
range” spread. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 2.0 if the current
price is above 2,20
NOTE: The formula would be =IF(G9>F9,2.0)